Daily DAX : Day 305 ISINSCOPE

Power BI DAX ISINSCOPE Function

Description

The ISINSCOPE function in DAX (Data Analysis Expressions) is used in Power BI to determine whether a specified column is currently in scope within a report's filter context, typically in a matrix or table visual with hierarchical levels. It returns TRUE if the column is in scope, and FALSE otherwise.

Syntax

ISINSCOPE(<column_name>)

• <column_name>: The name of the column to check if it is in the current filter scope.

Return Value

TRUE or FALSE (Boolean).

Use Case

ISINSCOPE is commonly used in hierarchical visuals (e.g., matrix or table) to create dynamic calculations that behave differently based on the level of hierarchy being displayed. It is particularly useful for customizing measures at different levels of a hierarchy, such as subtotals or grand totals.

Example

Suppose you have a sales table with a hierarchy of Year, Quarter, and Month. You want to display total sales for months but show a different calculation (e.g., average sales) for quarters and years.

Sales Measure = 
IF(
    ISINSCOPE('Date'[Month]),
    SUM('Sales'[Amount]),
    IF(
        ISINSCOPE('Date'[Quarter]),
        AVERAGE('Sales'[Amount]),
        CALCULATE(AVERAGE('Sales'[Amount]), ALL('Date'[Month]))
    )
)
    

Explanation of Example

• If Month is in scope (e.g., in the month level of a matrix), the measure returns the sum of sales.
• If Quarter is in scope (e.g., at the quarter level), it returns the average sales for that quarter.
• If neither Month nor Quarter is in scope (e.g., at the year or grand total level), it calculates the average sales across all months.

Key Points

• ISINSCOPE is useful for matrix visuals with row/column hierarchies.
• It helps create measures that adapt to the level of detail in the report.
• It evaluates the filter context to determine which level of the hierarchy is active.
• It is often used with other DAX functions like IF, SUM, or AVERAGE.

Notes

• ISINSCOPE only works with columns in the model, not measures.
• It is typically used in visuals with multiple hierarchy levels.
• Ensure the column referenced is part of the visual's hierarchy to avoid unexpected results.