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.
Hi Kasper,
thanks for great article.
Do you know is ISINSCOPE function could work with date hierarchy which is automatically build from Date object?
I don’t believe so. In general I would recommend creating your own date table anyway.
Hi Kasper,
One question. Is the isinscope function available for ssas tabular? I just tried to create a measure in my fact table but got the following error message:
“Failed to resolve name ‘ISINCOPE’. It is not a valid table, variable, or function name.”
DAX Expression:
SWITCH(TRUE,ISINCOPE(Time[Year]),”Year”,ISINSCOPE(Time[Month]),”Month”,”Other”)
We are running SQL Server 2017 on prem.
Thanks!
BR
Alex
it is not, it got introduced after Tabular got shipped. It only is available in Power BI or AS Azure and probably in the next release of SSAS.
Hi everybody,
I have a similar issue with calculate GrandTotal over a TOPN list that changes with drilldown of 4 dimensions.
how can i SUM only the visibles TOP5 values refering to the correspondent dimension at visible drilldown level.
Let’s explain:
I have a matrix with 4 dimensions with drilldown (TipoArtigo; Marca; Familia; Artigo)
I want to put the totals in the middle column (wich is a measure of TOPN margin of the dimension that is in each level of drill down. Basically I need to have only one measure that works for all 4 drill downs dimensions.
I tried with SUMX and TOPN to achive Totals only for TOPN of each dimension, but I couldn’t be able to achieve that. The problem is that in totals we don’t have row context and don’t know what dimension is in matrix lines at each moment, to sum only TOPN (5 in case) for that dimension.
I have 4 measures (one for each dimension) that work ok only when matches with the dimension in drilldown, that’s why I need a meaures for all dimensions.
1st column (TOPN HierarArtigo Margem) works ok in all dimensionsm but give the GrandTotal fo all the items and not only the visibles ones (TOP 5).
2nd column column (TOPN HierarArtigo Margem Parcial) is the one o don’t work on totals only for TOP 5 itens.
3rd column (TOPN Margem Parcial) work ok only with the corresponding dimension in lines otherwise gives only total (and wrong, because it’s the total that TOPN is supposed to calc based on the specific data_column).
In the follows print screens the 1st and 2nd measures area the same for all drilldowns, the 3rd changes….
Don’t worried because the grand total of the first column is lower than the parcial totals, because there are items with negative values (margin).
At the end I post all those measures.
I leave my original post here.
https://community.powerbi.com/t5/Desktop/MATRIX-GrandTotal-calculation-with-TOPN-multiples-dimensions-in/m-p/586735#M278100
Link for sample pbix file:
https://www.dropbox.com/s/4xgcpk3g694z1tn/Test.pbix?dl=0
Any help will be appreciate! Thanks
Hi everybody,
I have an issue using ISINSCOPE and TOPN to get the totals calculation.
How can i calculate the grand total with 4 levels of hierarchy using TOPN.
The problem is that at total level ISINSCOPE doesn´t work, and i need to calculate in the same measure, the total of TOPN (ex.5) for Product level; Family level; Brand level and Product Type level.
Here the link for my sample pbix file, with all the data and page report with the issue.
https://www.dropbox.com/s/ow2dfy1919ouazf/Sample%20Data.xlsx?dl=0
Can anyone help here please.
Thanks.
Is there a way to use ISINSCOPE or something similar in a visual that does not have the hierarchy present. I am trying to do a quantity calculation based on the unit of measure assigned at different levels in the hierarchy. At Level 1 the UOM might be M, however, Level 2 might have a different UOM and at Level 3 the item may have the same UOM as Level 1.
So when summing the qty at each level the qty should be represented at Level 1 but not at Level 2. The ISINSCOPE method shown here works perfectly when the items are in a matrix with a hierarchy.
If I try to put the same DAX expression in a visual without the hierarchy, the expression fails because the ISINSCOPE expressions for both fields return false since neither is in hierarchy in the visual. This occurs even if the visual is being filtered by a separate slicer. Is there any way to pass the filter level from the slicer so that the ISINSCOPE reads true.
I don’t think this is possible, but it is a real shame if so and hopefully there is a way to fix it in later upgrades. Please let me know if you can figure out a way to do this. If so great, if not, I will submit as an idea to Microsoft.
Thank you Kasper, your instruction really spared me a lot of pain!
I was struggling with my hierarchy data for a long time and now it seems to be solved within half an hour.
Thank you again and keep on the good work!
Agnes
Amazing article. Thank you for the help! This was the only place I could find an the level of detail I needed.
Hi,
This article was helpful with my matrix. However i need a matrix that filters a column chart, and this measure doesn’t work on the column chart. The matrix filters my chart, but no data appears on the chart. Can you please help?
Thanks
Hi Kasper,
Thank you sooooo much. I work with PowerBi only occasionally so struggled a lot with displaying predefined data based on a hierarchy. Your article had everything what I needed. I haven’t written one nice piece of Measure like you and ended up with six but… it works!
Thanks a million, 😊
Magda
Hi Kasper,
Maybe I missed something in the article but I’m bit confused about the hierarchy totals in the final example. Should the total for the country not be sum of figures in Mainstream and Online? Say for France it has figures for Mainstream (1032, 510) and Online (7035) which equals 8,577 but France total is 10,000.
And then the total for France and UK is 17,000 but Centre aggregate is 16,000.
As I said, maybe I missed something so apologies if that is the case.