Detecting Matrix Levels in DAX with ISINSCOPE

In a Matrix like above there are many places where you can show/hide values, when we think of it there are mainly 5 areas, Rows, Columns, Rows Total, Column Totals, and Grand Totals, but you can have many more levels by some boolean tricks.

So how do we control where exactly to show a value or which area to hide them from? by using ISINSCOPE.

This function not only tells whether you're grouping by a certain column at certain level but you can use it as boolean mask or an integer mask like a bitmap if you prefer.

Grouping? = 
ISINSCOPE ( Dates[Year] ) 
    + ISINSCOPE ( Products[Color] )

If you use this measure it acts somewhat like the GROUPING_ID in TSQL.

WITH YearColorScope AS (
	SELECT 
		D.Year,
		P.Color, 
		SalesAmount = SUM(S.NetPrice * S.Quantity),
		GroupingID = GROUPING_ID(D.Year, P.Color)
	FROM Sales AS S
		INNER JOIN Products AS P
			ON S.ProductKey = P.ProductKey
		INNER JOIN Dates AS D
			ON S.OrderDate = D.Date
	WHERE 1 = 1 
		AND P.Color IN ('Black', 'Gold', 'Pink')
		AND D.Year IN (2021, 2022)
	GROUP BY GROUPING SETS (
		(D.Year, P.Color),   -- Year + Color
		(D.Year),            -- Year total
		(P.Color),           -- Color total
		()                   -- Grand total
	)
)

SELECT *,
	GroupingLevel = CASE 
		WHEN GroupingID = 0 THEN 'Year + Color'
		WHEN GroupingID = 1 THEN 'Year Total'
		WHEN GroupingID = 2 THEN 'Color Total'
		ELSE 'Grand Total'
	END,
	YearScope = COALESCE(CAST(T.Year AS VARCHAR(10)), 'All Years'),
    ColorScope = COALESCE(T.Color, 'All Colors')
FROM 
	YearColorScope AS T
ORDER BY
	T.GroupingID,
    T.Year,
    T.Color;

But if you want a measure that also shows 3 and shows 0 for Year and Color then you can use:

Grouping ID = 
VAR YearBit =
    IF ( ISINSCOPE ( 'Dates'[Year] ), 0, 2 )
VAR ColorBit =
    IF ( ISINSCOPE ( 'Products'[Color] ), 0, 1 )
RETURN
    YearBit + ColorBit

And then pair it with:

Grouping Level = 
SWITCH (
    [Grouping ID],
    0, "Year + Color",
    1, "Year Total",
    2, "Color Total",
    3, "Grand Total"
)

To control in which or of the Matrix to show values we can create a new measure that detects the [Grouping ID] and uses SWITCH to present values.

ShowValue = 
VAR GroupingID = [Grouping ID]
VAR SalesAmount = [Sales Amount]
VAR Result =
    SWITCH (
        TRUE ()
        ,GroupingID = 1, SalesAmount            --"Year Total Only"
        ,GroupingID = 2, SalesAmount            --"Color Total Only"
        ,GroupingID = 0, SalesAmount            --"Color + Year"
        ,GroupingID IN { 0, 1 }, SalesAmount    --"Year w Total"
        ,GroupingID IN { 0, 2 }, SalesAmount    --"Color w Total"
        ,GroupingID = 3, SalesAmount            --"Grand Total"
        ,GroupingID IN { 1, 2 }, SalesAmount    --"Sub Totals"
        ,GroupingID <> 0, SalesAmount           --"All Totals"
    )
RETURN
    Result

By Evaluating one line at a time you can see where the values are shown.

When the Grouping ID is 1 we show the sum of all the columns, i.e. Column Subtotal.

When the GroupingID is 2 we only show Rows Subtotal.

When the GroupingID is 0 we show cross section of Rows and Columns without subtotals.

If we want to show Rows and Columns with only Column subtotals we use 0 or 1

For showing Colors and their subtotals we use 0 or 2:

To show Values only at the grand total:

Only Subtotals:

All Totals:

If 0, 1, 2, 3 are a bit difficult to remember then you can also use this technique which relies on Boolean mask.

ShowValue Bool = 
VAR YearLevel = ISINSCOPE ( Dates[Year] )
VAR ColorLevel = ISINSCOPE ( Products[Color] )
VAR SalesAmount = [Sales Amount]
VAR Result = 
    SWITCH ( 
        TRUE()
        ,YearLevel, SalesAmount                          -- "Year"
        ,ColorLevel, SalesAmount                         -- "Color"
        ,ColorLevel && YearLevel, SalesAmount            -- "Color Year"
        ,NOT ColorLevel && YearLevel, SalesAmount        -- "Year Total"
        ,ColorLevel && NOT YearLevel, SalesAmount        -- "Color Total"
        ,(ColorLevel || YearLevel) 
            && NOT(ColorLevel && YearLevel), SalesAmount -- "Sub Totals"
        ,NOT ( ColorLevel && YearLevel ), SalesAmount    -- "All Totals"
        ,NOT ( ColorLevel || YearLevel ), SalesAmount    -- "Grand Total"
    )
RETURN 
    Result

Another use case of being able to detect the level at which you're browsing in a visual is when you want to debug your code with EVALUATEANDLOG, since EVALUATEANDLOG allows you to name each call it makes it easy to identify which execution pertains to which part of the code.

ShowValue LOG = 
VAR YearLevel = ISINSCOPE ( Dates[Year] )
VAR ColorLevel = ISINSCOPE ( Products[Color] )
VAR SalesAmount = [Sales Amount]
VAR Result = 
    SWITCH ( 
        TRUE()
        ,YearLevel, 
            EVALUATEANDLOG ( SalesAmount, "Year" )

        ,ColorLevel, 
            EVALUATEANDLOG ( SalesAmount, "Color" )

        ,ColorLevel && YearLevel, 
            EVALUATEANDLOG ( [Sales Amount], "Color Year" )

        ,NOT ColorLevel && YearLevel, 
            EVALUATEANDLOG ( SalesAmount, "Year Total" )

        ,ColorLevel && NOT YearLevel, 
            EVALUATEANDLOG ( SalesAmount, "Color Total" )

        ,(ColorLevel || YearLevel) 
            && NOT ( ColorLevel && YearLevel ), 
                EVALUATEANDLOG ( SalesAmount, "Sub Totals" )

        ,NOT ( ColorLevel && YearLevel ), 
            EVALUATEANDLOG ( SalesAmount, "All Totals" )

        ,NOT ( ColorLevel || YearLevel ), 
            EVALUATEANDLOG ( SalesAmount, "Grand Total" )
    )
RETURN  
    Result

As you can see from the following image, if you are using "DAX Debug Output" you can track what is happening at Sub Totals.