ISINSCOPE function was introduced in with November 2018 Power BI update. We can say it's a very young player in the game, but it's definitely magic. It can be used to manage lots of issues related to hierarchies, but in this post, I'm going to focus on manipulating the grand total line with it.
This is my budget forecast table. The budget forecast that you see for every month is not the forecast for every month, but it's the forecast for the whole year. The budget forecast for 2020 has changed every month. That's why we have different numbers for every row.
Below you see the table visual with the year-month column and a measure that shows the values for every month. The thing that I want you to pay attention to is the total line. I wanted to see the last month's value for the total line and this is the measure for that.
This measure calculates this: If the year-month column has one value then take the average of that value. If it doesn't have one value, like the total line, which has 12 values in this situation, show 32000. The other thing that worths mentioning is, we can use MIN, MAX, or SUM instead of the AVERAGE function. Because in the current context, on the table, there will be one number for every row. Min, max, sum, or average value of a number (for example 15000) is the same. That's why it doesn't matter which one we chose. That being said, you see that my measure works fine. I got what I want. Why am I even writing this post?
The problem arises when I filter the table with only one value. Because the measure shows the selected value from the forecast column when the year-month column has one value. Actually, the computer is doing exactly what I asked for. I don't have that problem when I filter the table with multiple values, because multiple values don't fall into the criteria of having one value.
How to solve it? I should tell Power BI to treat the total line differently than the year-month column. That's where ISINSCOPE function comes into play. Microsoft's definition for the function is: 'Returns true when the specified column is the level in a
hierarchy of levels.' The syntax is very simple:
ISINSCOPE(<columnName>)
To solve the problem. I have changed HASONEVALUE part of my formula with ISINSCOPE function. After that, I can still see 32000 on the total line even if I choose one value on the slicer.
How is that possible? The best way to understand is, to think of the total line as an extension of the year-month column with HASONEVALUE function, but as an independent area with ISINSCOPE function. When we use ISINSCOPE, like it's mentioned in the definition, it returns true only if the column is the level of a hierarchy. Obviously, the total line is not. But it's treated as part of that column with HASONEVALUE function.
As you see, with ISINSCOPE function the total line 'is not in scope' no matter it's filtered or not. If you need to manipulate your total line, this function comes in handy. Like I mentioned in the beginning and like Microsoft's definition gives the clue, its real power comes from handling hierarchical problems. My next post is going to be about it. If you'd like to know more about this small magic, stay tuned.
Comments