SCOPING at different granularities in DAX (Part II)

As mentioned on the prior post on the topic (available here), the DAX calculation that mimics MDX scope behavior requires a certain ‘filter arrangement’ to work properly.  In other words, it actually expects a display hierarchy -  otherwise it will not yield the correct results. Can we overcome this to more closely resemble MDX behavior? Interestingly, yes – is it possible, using a variation of the formula exposed on the prior entry.

image

Let’s examine this issue a bit closer:  Continuing with the MDX scope Adventure Works example which was used in the last blog entry, I am able to query the month directly and still get the assigned cell values even if the quarter is not part of the query expression:

image

With the DAX solution we used, however, we are unable to get the same results as we expect to Fiscal Quarter to be part of the current query context. Lets first remember the correct output.  Notice we are using a fiscal hierarchy of year, quarter and month:

image

As you may recall, the calculation limits itself to clearing the filter context on month (highlighted below), which forces the engine to use the next display hierarchy level available to allocate the calculation context. This happens to be quarter in our example. Once we grab that value, we split it by the number of months belonging to that quarter:

SUMX(
    VALUES(DimTime[EnglishMonthName]),
    CALCULATE(
        SUM( FactSalesQuota[SalesAmountQuota]  ) / CALCULATE( COUNTROWS( VALUES( DimTime[EnglishMonthName] ) ), ALL( DimTime[EnglishMonthName] ) ), 
      
ALL( DimTime[EnglishMonthName] )
    )
)

If we remove the quarter from the query context, we get:

image

Clearly not matching MDX results. So how can we truly assign a value derived from a quarter level assignment to the monthly level, regardless if quarter is in the resultset or not? One way of doing this is by leveraging the SUMMARIZE function.  With it, we can build a table on-the-fly at the appropriate level required and use it as a ‘set filter’ parameter to the CALCULATE function:

Scoping v2(SUMX):=SUMX(

    VALUES( DimTime[EnglishMonthName] ),

        CALCULATE( SUM( FactSalesQuota[SalesAmountQuota] ) ,

            ALL(DimTime),

            SUMMARIZE(DimTime, DimTime[FQ] )

        )

    /

        CALCULATE( DISTINCTCOUNT( DimTime[EnglishMonthName] )  ,

            ALL(DimTime),

            SUMMARIZE(DimTime, DimTime[FQ] )

        )

)

Notice that Fiscal Quarter ([FQ]) is called within the SUMMARIZE function.  As such, the calculation will always use fiscal quarter values, regardless if fiscal quarter is used or not in the query context. This is not too different from the MDX Scope definition used in Adventure Works:  on it, a reference to the Parent level of the CurrentMember is used, and as the Scope is defined for the Month level, the resulting output always takes the Quarter into consideration.

The resulting DAX output matches MDX Scope:

image

The pattern of - 

AggregateX( <granularity at which the expression executes>, <expression>)

is still being used here.  So for each cell output displayed, the calculation always executes at the monthly level of granularity even if what I am showing is quarterly or yearly data.

In order to ensure the pattern works, lets test a few more things.   First, lets add the Fiscal Year below the month:

image

We see that the new calculation (Scoping v2), is able to keep its context appropriately even in this scenario.

As a second test, lets place the Calendar Year and Calendar Quarter in the row labels instead of the Fiscal Year and Fiscal Quarter.   With the initial calculation, the current query context will dictate what will be used to compute the monthly values.  However, as the new calculation explicitly calls the Fiscal Quarter as part of the expression, the values will still be correct even in this case.  In order to make the solution easier to understand, I added an extra measures called ‘Current Fiscal Quarter’ to the model with the following definition:

Current Fiscal Quarter:=IF(HASONEVALUE(DimTime[FQ]),

                                                   VALUES(DimTime[FQ]) )

image

Now, if we place the Calendar Year and Quarters in the row labels, we get:

image

As you can see, monthly values are still evenly assigned from the Fiscal Quarter, even though the fiscal quarter is not part of the slicing hierarchy at all.

As mentioned on the prior post,  Jason and I will be using a similar pattern to tackle some interesting Last Non Empty Scenarios.  That, however, will be the subject of a future post Smile

Advertisements

6 Responses to SCOPING at different granularities in DAX (Part II)

  1. Pingback: SCOPING at different granularities in DAX (Part I) « Javier Guillén

  2. Marco Russo says:

    Very nice approach. This example well express the main difference between DAX and MDX. In DAX you have to put the logic of the hierarchy in the query, whereas in MDX it has to be in the data model. Just like SQL has relationships in query whereas MDX has relationships in the model and DAX can have both.

  3. Lee Hawthorn says:

    This is a great article. Thanks for writing it. In the real world an additional requirement for this would be to combine additional facts at a different granularity i.e. Actual Sales. In this example, I added the FactResellerSales table to the model with a relationship to DimTime, DimSalesTerritory, DimEmployee.

    This meant the relationship coming from the direction of FactSalesQuota had to be inactive between DimEmployee and DimSalesTerritory.

    I simply added : userelationship(DimEmployee[SalesTerritoryKey],DimSalesTerritory[SalesTerritoryKey]) to each of the calculate functions in the Sales Quota measure and all is working.

    There’s so much more of DAX for me to learn!

    Lee

    • javierguillen says:

      Good point. It illustrates the flexibility that can be achieved through relatively small changes to a DAX formula. Thank you Lee!

  4. Pingback: SCOPING at different granularities (Part III) « Javier Guillén

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: