In the November release of Power BI desktop we introduced a new DAX expression that is really great. Working with hierarchies in DAX have always been a bit of a pain, especially in scenario’s where you need to change your calculation based upon the level that you are in.
Let’s take a scenario where the business has some special rules to calculate totals. For each total in the hierarchy the results are pre calculated (a simple sum would be incorrect), not unlike unary operators or custom role ups as we have them in AS Multi Dimensional.
We start with a hierarchy like this. As mentioned before the totals are already calculated so we will need a row in the table for each of them. To be able to do the right calculation we need a way to know which row is a total or not so I added a IsTotal column.
For each level in the hierarchy we have a value in the fact table:
Next I create a relationship and create a hierarchy .
Finally I drag in the Values column and the Hierarchy in a matrix (I also turned on the +/- icons so we can expand collapse which is another November feature). The first thing we see is that the matrix looks weird with blanks and all.
The reason we are seeing this is this is how the data is set up, we have stored totals and grand totals at the lowest level in the hierarchy. Instead we want to show to them at the level where they are correct. Also we don’t want to show the aggregation created by the SUM.
To do this we need to use DAX to determine the level we are at based on that determine the calculation to do. We can use the DAX function ISFILTERED to check whether we are at a certain level but this function has as problem it also listens to “filters” set on the report like slicers so it might give you false positive at wrong levels, so that is not great either.
Alternatively we can check if you are at a level by counting the number of values for a particular column. If it is 1 you know you are at that level. The DAX would look like this:Measure 2 =
COUNTROWS ( VALUES ( Sellers[Seller] ) ),
ALLEXCEPT ( Sellers, Sellers[Seller] )
or written with a simple shorthand (does the same as above but encapsulated in a single function):Measure 2 =
IF ( HASONEFILTER ( Sellers[Seller] ), TRUE () )
This measure results in true at the lowest level, it is using the ALLExcept to clear any filters set on the seller from the outside (like a slicer).
With the recent update of Power BI and the IsInScope function we have made this even easier. Instead of the complicated expression above you can write this:Measure 2 =
IF ( ISINSCOPE ( Sellers[Seller] ), TRUE () )
IsInScope can even detect more cases and cannot be expressed using any existing DAX functions which depends on the detection of filter context and/or the remaining column values after filters are applied. IsInScope returns true if a column is in the filter context and it is a grouping column in the current row of a query resultset. This information cannot be derived from filter context alone.
Returning the right calculation per level
Finally if you want to test any other level then the bottom you will need to test for both. Here is what happens when I test for Channel:
As you can see both return true, which is understandable as both are in scope. To solve this you have to test for both. Like this (I am using the Switch True trick again):Measure 2 =
ISINSCOPE ( Sellers[Seller] ), “seller”,
ISINSCOPE ( Sellers[Channel] ), “channel”,
This now allows us to see which level I am at:
Putting it all together in a DAX expression that returns a different calculation for each level.Measure =
VAR grandtotal =
CALCULATE ( [Sum of Value], Sellers[IsTotal] = 3 )
VAR sellersum =
CALCULATE ( [Sum of Value], Sellers[IsTotal] = 0 )
VAR sellerselected =
ISINSCOPE ( sellers[Seller] )
VAR channelselected =
ISINSCOPE ( sellers[channel] )
VAR Countrysum =
[Sum of Value],
Sellers[IsTotal] = 1,
ALLEXCEPT ( Sellers, Sellers[COUNTRY] )
VAR Countryselected =
ISINSCOPE ( sellers[Country] )
VAR Regionsum =
[Sum of Value],
Sellers[IsTotal] = 2,
ALLEXCEPT ( Sellers, Sellers[Region] )
VAR Regionselected =
ISINSCOPE ( sellers[Region] )
channelselected, BLANK (),
By using the IsTotal field we added before to get the values for each level in the hierarchy and ignore any other values, that makes sure we don’t aggregate any children. The ALLEXCEPT make sure we ignore any filters except the ones set on the level we are looking for, this might or might not be needed in your scenario. Finally this gets us this result:
It now shows the values for each level based on our custom calculation, not using the regular sum. Pretty cool 🙂
You can download the sample file here.