SCOPING at different granularities in DAX (Part III)

In the last two blog entries on this topic (available here and here), we got a bit closer to reproducing – in DAX – the same output one would normally achieve with the MDX scope statement.

In this new entry, we perfect the formula a bit more by ensuring filter context propagates to the higher levels of granularity we are affecting.  Thanks to Gerhard Brueckl ( blog ) and Jason Thomas ( twitter | blog ), who contributed greatly to this blog entry and helped point out areas of improvement.

With the last formula we used, we are able to retain the Fiscal Quarter context (and split its value evenly among the months that belong to it), even if fiscal quarter is not part of the query context.

There is one issue with the formula, though:  if a user modifies the query context to include only two months for example, each belonging to a different quarter, the total for the year would be incorrect.  Let’s take a look:

image

If we manually add the quarter totals we can see the fiscal year value is off.   What is happening here?  We must realize that we are ‘scoping’ at the month level: through the use of

SUMX ( VALUES ( <month> ), <expression invoking fiscal quarter> )

we are:

  1. iterating over a list of monthly values and
  2. making a reference to the quarter(s) at the current filter context

With this understanding we can now see how at the Fiscal Year level the context will contain two quarters.  As such, each iterated month will be accounted twice.   The solution is a VERY simple one, yet not immediately intuitive:   We must wrap our CALCULATE expression with another CALCULATE.  In this way, the ‘current month’ in context will only refer to the actual fiscal quarter that contains it – avoiding ‘double counting’ of data.

Here is the final expression:

Scoping v3 (SUMX):=SUMX(
    VALUES( DimTime[EnglishMonthName] ),
    CALCULATE( CALCULATE( SUM( FactSalesQuota[SalesAmountQuota] ) ,
            ALL(DimTime[EnglishMonthName]),
            SUMMARIZE(DimTime, DimTime[FQ] )
        )
    /
            CALCULATE( DISTINCTCOUNT( DimTime[EnglishMonthName] )  ,
                ALL(DimTime[EnglishMonthName]),
                SUMMARIZE(DimTime, DimTime[FQ] )
            )
) )

Notice the double CALCULATE on the third line.  We are:

1) computing an expression based on modified filters (the first CALCULATE) and then…

2)  converting row context into filter context so each Month gets assigned the appropriate value (the second CALCULATE).

image

Whenever you are “scoping” at a specific level in DAX, you must use a double CALCULATE pattern in order to propagate context appropriately at levels higher than the one you are currently affecting.

With this change, context now gets properly established at the year and grand total levels:

image

Finally we must consider not only the levels above month (quarter, year), but also the levels below (day).   As shown in the first part of this blog series, the SSAS cube dimension usage is assigned, in Adventure Works’ Sales Targets measure group, at the quarter level of granularity.  This in effect leaves date level members with no sales target values (even though the actual relationship happens at the daily grain in the DSV):

image

We can modify the DAX calculation to mimic the same behavior.  Instead of modifying the Tabular model itself, we can simply add a condition to our calculation so it will only execute at monthly levels and above, but never at the daily level:

Scoping v4:=SUMX(

    VALUES( DimTime[EnglishMonthName] ),

    CALCULATE(IF(NOT(HASONEVALUE(DimTime[FullDateAlternateKey])) ,

            CALCULATE(

                SUM( FactSalesQuota[SalesAmountQuota]) ,

                SUMMARIZE(DimTime, DimTime[FQ] ) ,

                ALL(DimTime ) /

            CALCULATE( DISTINCTCOUNT( DimTime[EnglishMonthName] )  ,                

                SUMMARIZE(DimTime, DimTime[FQ] ),

                ALL(DimTime)

                )            

            ) ) )

 

Notice the only change in the calculation is the IF statement on the third line.  With it, we now are able to fully match MDX behavior at any level of a Calendar hierarchy.  If you are in need to compute a DAX expression at a specific level of granularity on your Tabular model, consider using a similar pattern as the one exposed above.

image

Finally, if we want to simplify this pattern we can replace the outer CALCULATE with SUMMARIZE.   As both of them have the ability to turn row context into filter context – which is needed to accurately compute at all levels – we can use it to generate a more readable version of the calculation – Thanks Gerhard for the suggestion!

SUMX(

SUMMARIZE(DimTime, DimTime[EnglishMonthName], "CalculatedMonthValue",

IF(    NOT(HASONEVALUE(DimTime[FullDateAlternateKey] ) ),

CALCULATE(

        SUM(FactSalesQuota[SalesAmountQuota])/DISTINCTCOUNT(DimTime[EnglishMonthName]),

        VALUES(DimTime[FQ]),

        ALL(DimTime))) ),

[CalculatedMonthValue]
)

Advertisements

4 Responses to SCOPING at different granularities in DAX (Part III)

  1. Pingback: LastNonEmpty in Tabular mode: Part 2, Last Ever Non Empty calculations in DAX « Javier Guillén

  2. John Bradley says:

    Javier, I am trying to work through your examples and maybe I am missing something. If I have a list of coaches and their clients that they are working with and I want to SUM the time spent with each client at the client level but at the coach level I want to SUM the total time spent with all their clients divided by the number of clients they are working with to get an average at the coach level. I used your ISFILTERED technique discussed on the first post but I also want to have the total Average time spent with all clients could you use the a FUNCTIONX approach to solve the problem.

  3. John Bradley says:

    I sent an email with the attached data and a PivotTable Mockup of what I am trying to get.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: