A comparative example of solution in DAX: standard vs. WINDOW-function approach

The WINDOW functions in DAX, (OFFSET, INDEX and WINDOW) have already been described in a previous article (only available in Italian). To help readers understand its power, in this article we will address a practical case whose solution is, following the only possible approach before the advent of WINDOW functions, quite complex and, using WINDOW functions, quite simple.

The model consists of a single table. This is not a suggestion to work with a single table: Autoexist is always lurking and a star-schema is always better, but here we want to reproduce the problem exactly as reported and fixed.

The first rows of the table, named Data, are visible in figure 1.

The table has 4,157 rows and shows the requested delivery date (Data[Requested Date]), the order type (Data[OrderType]), the ordered product (Data[Product]), the ordered quantity (Data[Quantity]) and the description of the order type (Data[Order Type Description]). The quantities in each row can be positive or negative.

Scenario

The issue surfaced in the Power BI Community, where an announcement asked to create a measure that allows you to look at the cumulative value of quantities, regardless of the columns used to group. We have responded to the support request and partially solved the issue.

In fact, unfortunately, it is not possible to fully resolve the request because Tabular does not yet allow you to read the metadata; the ISFILTERED ( ) condition could certainly be verified for each single column of the model, but if a new calculated or imported column were then added, the problem would re-occur. In this article, therefore, we present the steps taken to offer a semi-solution: it was decided, in agreement with who asked for support, to go so far as to use three columns: Data[Requested Date], Data[Product] and Data [Order Type Description]. We will take it one step at a time, starting with a single grouping column, with the no-WINDOW-function approach. Finally, the version that uses the WINDOW functions will be shown, in particular the WINDOW function. This way you will appreciate the simplification of the code. For the same reason, the classic approach DAX code is not documented nor explained in detail.

Below is a Power BI Desktop table visual with grouping only by Data[Requested Date], a quantity aggregation measure (QTY) and the first version of the solution (Cumulated QTY V1).

The code of the measures shown in figure 2 is hereafter.

QTY = Cumulated QTY V1 =

The complexity is, up to this point, quite low for those who know a little DAX. However, it grows considerably – following the classic DAX approach – upon entering the product and then the order type description, making the first version of the solution incorrect (figure 3).

The Cumulated QTY V1 measure no longer works because the product is now present in the filter context. Cumulated QTY V2, which works in this context and represents the second step of the solution, is much more complex than Cumulated QTY V1 because it must establish an ordering by date requested and by product (in the logic of the following code, the description of the order type has also been considered, so that this is the ultimate measure that fixes the problem with the classic DAX approach):

Cumulated QTY V2 =

Without commenting the above DAX code for the reason described at the beginning of the article, finally, the insertion of the Data [Order Type Description] column in the report does not affect the correctness of the Cumulated QTY V2 measure, as the code has already provided for having, in the filter context, the order type description (figure 4).

Without further comment, here is the alternative code version to Cumulative QTY V2 that uses the WINDOW function:

Cumulated QTY WINDOW =
VAR QTY =
    SUM ( Data[Quantity] )
RETURN
    IF (
        NOT ( ISBLANK ( QTY ) ),
        CALCULATE (
            SUM ( Data[Quantity] ),
            WINDOW (
                1,
                ABS,
                0,
                REL,
                ALLSELECTED (
                    Data[Order Type Description],
                    Data[Order Type],
                    Data[Product],
                    Data[Requested Date]
                ),
                ORDERBY ( Data[Requested Date], ASC,
                Data[Product], ASC,
                Data[Order Type], ASC,
                Data[Order Type Description], ASC )
            )
        )
    )

Here is the measure in action (figure 5):

The Cumulated QTY WINDOW measure provides the same results as Cumulated QTY V2 and works, like the latter, in each of the contexts described up to now. However, in addition to being written in shorter and simpler code than Cumulated QTY V2, Cumulated QTY WINDOW is easier to interpret and maintain..

Conclusions

The advent of the WINDOW functions brings about a good simplification of DAX code in some scenarios. This is probably not the most commonly used case as the request appears a bit unusual; however, the DAX code is simplified and we believe it was a useful example to understand the potential of WINDOW functions, which we remember are still in preview at the date of publication of this article.

We haven’t yet performed performance testing of WINDOW functions against the classic DAX approach, using DAX Studio. We will do it on a larger database to make the estimate meaningful and we will publish the results. We recommend that you study the classic DAX code shown, for the purpose of getting confidence with the table functions and RANKX. We welcome suggestions to simplify or improve it.

May DAX be with You!

Autore del Post