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.

## The scenario

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.

## The DAX

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 =IF (

CALCULATE (

COUNTROWS ( VALUES ( Sellers[Seller] ) ),

ALLEXCEPT ( Sellers, Sellers[Seller] )

)

= 1,

TRUE ()

)

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 =SWITCH (

TRUE,

ISINSCOPE ( Sellers[Seller] ), “seller”,

ISINSCOPE ( Sellers[Channel] ), “channel”,

“other”

)

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 =

CALCULATE (

[Sum of Value],

Sellers[IsTotal] = 1,

ALLEXCEPT ( Sellers, Sellers[COUNTRY] )

)

VAR Countryselected =

ISINSCOPE ( sellers[Country] )

VAR Regionsum =

CALCULATE (

[Sum of Value],

Sellers[IsTotal] = 2,

ALLEXCEPT ( Sellers, Sellers[Region] )

)

VAR Regionselected =

ISINSCOPE ( sellers[Region] )

RETURN

SWITCH (

TRUE (),

sellerselected, sellersum,

channelselected, BLANK (),

countryselected, Countrysum,

regionselected, Regionsum,

grandtotal

)

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.

Hello Kasper,

thank you very much for your article about the new DAX expression “IsInScope”. It could be that this might be indeed a way to replace the oldie but goldie “Art of the Cascading Subtotal” from David Churchward.

https://powerpivotpro.com/2011/09/profit-lossthe-art-of-the-cascading-subtotal/

Since basically my entire reporting depends on this concept, I would like to hear your opinion before I make any adjustments; with the previous hierarchies in DAX I always had some problems in the concrete implementation.

Hartelijk dank voor een korte feedback.

Groetjes,

Rüdiger

Hi Kasper, looks great !

Is there any way to uses this logic to also change between different Tooltips based on the IsInScope value?

Thanks.

Best,

Moritz

yes this should be possible the tooltip is also filtered by the filter context.

Hi Kasper, thanks for this..very intuitive. My issue is that I have a 9 level ragged hierarchy that I built in both tabular model and in PowerBI by flattening to the lowest level. For the life of me I am unable to sort the different levels as this is a major requirement for the P&L statement. Would you have any idea of what one will need to do to sort and apply the sort to a Power BI matrix?

Help will be much appreciated.

Regards

Troy

Hi Kasper,

I would like to chime in here. Great articel, thanks for it.

In reply to Rüdiger I would like to raise the very same question. It seems that it may be possible to have hierarchies that may properly sum up subtotals as they appear in a rolling P&L. However, it has yet to be proved whether the isinscope function will further enable us to write such a measure.

Brgds,

Guido

I’ll see if I can add a P&L example blog post with a sample.

Kasper, Seller_ID 9 to 14 shall be aggreagated automatically. I’ve seen in your example that there are already values in the fact table provided. However, if one wants to build a P&L there are no summed values of the parents in the fact table but all aggregated sums need to be calculated on the fly accordingly to the definitions in the sellers table.

Brds,

Guido

understood, this sample is not trying to represent a P&L statement. IT is designed for a different use case.