SCOPING at different granularities in DAX (Part II)
May 4, 2012 6 Comments
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.
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:
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:
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:
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:
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:
VALUES( DimTime[EnglishMonthName] ),
CALCULATE( SUM( FactSalesQuota[SalesAmountQuota] ) ,
SUMMARIZE(DimTime, DimTime[FQ] )
CALCULATE( DISTINCTCOUNT( DimTime[EnglishMonthName] ) ,
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:
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:
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]),
Now, if we place the Calendar Year and Quarters in the row labels, we get:
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