Power BI > Use of ISINSCOPE()

, August 28, 2020 5194 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] )
    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] )
IF (
       ISINSCOPE ( 'Product Sales'[Sub-Category] )
           ISBLANK ( SELECTEDVALUE ( 'Product Sales'[Sub-Category] ) ),
       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!



INKEY is your solution partner.
Our focus is to deliver you in-time intelligent innovative solutions ("key") for the problems in hand. Maintaining a quality standard right from the inception of a project is our top most priority.

Our team of talented professionals will execute your projects with dedication and excellence. We take ownership and accountability for the effort that goes into meeting our client’s needs.

Years of experience and proven success of delivering innovative custom solutions.

More posts by

One response to “Power BI > Use of ISINSCOPE()”

  1. eladio bardelli says:

    Excelente tips y muy bien explicado, gracias saludos desde Chile.

Leave a Reply

Your email address will not be published.

The maximum upload file size: 2 MB. You can upload: image, audio, video, document, spreadsheet, interactive, text, archive, code, other. Drop file here

Want to know how we can help move your business to cloud? Enter your email address below