Conditional Formatting – ISINSCOPE

ISINSCOPE is a function that returns true when the specified column is the level in a hierarchy of levels. In simple words, ISINSCOPE is used to get the right hierarchy level.

Use Case:

Recently, we came across a request where the conditional formatting (icons) should be displayed only in subtotal but not in grand total of a particular hierarchy level. Let’s discuss here how we solved the scenario in a simple way.

Solution:

In our report, we have a matrix with five hierarchy levels (Department, Practice, Resource, Project, and Division). Here the data is displayed on week basis (at the start of the week). The icon should be displayed only in the Row sub-total but not in the grand total while drill-down to Project and Division Hierarchy level. We tried to achieve this in many ways. But it didn’t give the result we expected. Later we tried with ISINSCOPE function. Guess what, we achieved the output as we expected. Below is the Dax (Measure) we used to achieve the icon to be placed in the sub-total level.

The above Dax is given as input to add the icon (conditional formatting) in the required area.

The above Dax (Icon) is given as a field value in Conditional formatting.

Level1: No Icons and No Sub total

Level2: No Icons and No Sub total

Level3: No Icons and No Sub total

Level4: Icons added in Subtotal

Level5: Icons added in Subtotal but not in grandtotal