Blogs

Power BI > Use of ISINSCOPE()

Inkey, August 28, 2020 8789 Views

Power BI has advanced incredibly in the last few years. Microsoft has been working their fingers to the bone to make Power BI simple to use and easily adaptable. But at times, it gives us hard time to achieve even the simplest of requirements, or at least we think so.

Recently, I faced one such issue.
Let’s get right into it.

Let’s assume, we have a table with 3 columns – Category, Subcategory & Sales.
And we have to show Sales for each Category & Subcategory. It seems the easiest task to do, right?
Let’s move on to achieve the requirement.

But I do not want to show the blank value when there are no Subcategories and the total value shows the Sales value twice – once for Category & once for SubCategory as highlighted in the image below:

But the expectation is as below:

So the next step would be to check whether the value of Subcategory is blank or not.
Let’s create a measure as below:

IsSubcategoryblank = ISBLANK(SELECTEDVALUE('Product Sales'[Sub-Category]))

Let’s see what happens after plotting it in the matrix.

It shows the ‘True’ value for both Category and Subcategory values. So, if we use that condition in the measure and see if the value of Subcategory is blank, then it would give us blank values for both the Category and Subcategory values.

Let’s keep it in the measure, plot it in the matrix, and see what happens.

Total Sales =
VAR SelectedSubcategory =
    SELECTEDVALUE ( 'Product Sales'[Sub-Category] )
RETURN
    IF ( ISBLANK ( SelectedSubcategory ), 0, SUM ( 'Product Sales'[Sales] ) )

But it didn’t fetch the correct result.

There’s a DAX function – ISINSCOPE which returns true when the specified column is the level in a hierarchy of levels.

Let’s create a new measure which uses ISINSCOPE and plot it in the matrix.

IsInScope = ISINSCOPE('Product Sales'[Sub-Category])

Here, we can see that we are getting ‘True’ value where the Subcategory value is blank because Subcategory is in the hierarchy of the matrix.
Notice that it gives ‘False’ value for the Category value unlike the result we get when we use ISBLANK.

Now, we can use both the conditions in the measure and return the Sales value when both the conditions give ‘False’.
So, now the measure can be as below:

Total Sales =
VAR SelectedSubcategory =
   SELECTEDVALUE ( 'Product Sales'[Sub-Category] )
RETURN
IF (
       ISINSCOPE ( 'Product Sales'[Sub-Category] )
           &&
           ISBLANK ( SELECTEDVALUE ( 'Product Sales'[Sub-Category] ) ),
       BLANK(),
       SUM ( 'Product Sales'[Sales] )
   )

Let’s see what result it gives:

And boom! We get the expected result!

Hence, ISINSCOPE is DAX function which is used not often but is very useful when it comes to hierarchy.

Happy DAX-ting!