ISINSCOPE DAX Function

The ISINSCOPE function in DAX is used to determine whether a specified column is currently being used to provide context to the calculation.

This is particularly useful in scenarios involving hierarchical data or when you need to conditionally format calculations based on the presence of a specific column in the current filter context.

Returns true when the specified column is the level in a hierarchy of levels. This function is categorized under the Time Intelligence DAX functions.

Note: This function is not supported for use in Direct Query mode when used in calculated columns or row-level security (RLS) rules.

Syntax

ISINSCOPE(<columnName>)

Parameters

columnName : The name of an existing column, using standard DAX syntax. It cannot be an expression.

Let’s get started

Import below dataset into Power BI Desktop

Example Scenario:

Imagine you are working with a dataset containing sales information categorized by region, country, and city, and you want to create a measure that calculates total sales, but only if the data is scoped down to the city level.

Now  follow these steps:

Step-1: Add one measure for Total Sales at city level.

TotalSalesAtCityLevel =
IF(
    ISINSCOPE(Sales[City]),
    SUM(Sales[Sales]),
    BLANK()
)

Step-2: Add a table visual and drag all columns along with the newly created measure.

Now you can see the above screenshot. In the first table visual, we have the city-level column, which fulfills the DAX condition, which is why DAX is returning the data.

In the second table visual, we don’t have city-level data, which is the reason DAX is returning a blank output.

Conclusion

The ISINSCOPE function is highly useful when dealing with hierarchical data and conditional calculations based on the level of detail in the filter context.

Another Scenario: Return values in the matrix visual only for the lowest level of the hierarchy.

Add a matrix visual with region, country, city, and sales columns.

As you can see in the screenshot above, values are repeating at the region and country levels. However, you want to see values only at the lowest level of hierarchy, which is the city level.

Now, add the TotalSalesAtCityLevel measure in the matrix visual that we created in the above example.

As you can see in the above screenshot, DAX is returning data only for the low-level hierarchy, which is city, and for other hierarchies, it is returning a blank value. Because in our DAX code, we passed the name of the city column in ISINSCOPE.

I hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome.