Using NONVISUAL in DAX

DAX has an interesting function named NONVISUAL, which marks a Filter in SUMMARIZECOLUMNS to impact only the grouping columns and not affect the measures.

We have model where there are Years in the Dates[Year] from 2020 - 2026 but the Sales only occured for 2021 - 2025, so when a user writes a DAX Query like the following one it returns 5.

DEFINE 
	MEASURE Sales[#Years in Dates] = 
		COUNTROWS ( ALLSELECTED ( Dates[Year] ) )
		
EVALUATE	
	SUMMARIZECOLUMNS ( 
		ProductCategory[Category],
		Dates[Year],
		SUMMARIZE ( Sales, Dates[Year] ),
		TREATAS ( { "Audio" }, ProductCategory[Category] ),
		"Sales", [Sales Amount],
		"#Years in Dates", [#Years in Dates]
	)
ORDER BY 
	ProductCategory[Category] ASC,
	Dates[Year] ASC

But what if the user wants to print 7, what do we do here? Change the measure to use ALL instead of ALLSELECTED? Maybe we can for this scenario but not always, right?

Enter NONVISUAL that only filters the grouping column in our case Dates[Year] and not the Measures.

By marking a filter in SUMMARIZECOLUMNS as NONVISUAL we can avoid manipulating the code. Now it returns 7 that's the DISTINCTCOUNT of years in that column.

And in case if you still want to show 5 years that have at least 1 row in Sales you can use this:

Complete code:

DEFINE 
	MEASURE Sales[#Years in Dates] = 
		COUNTROWS ( ALLSELECTED ( Dates[Year] ) )
		
	MEASURE Sales[#Years in Sales] = 
		CALCULATE ( 
			COUNTROWS ( SUMMARIZE ( Sales, Dates[Year] ) ), 
			ALLSELECTED () 
		)
		
EVALUATE	
	SUMMARIZECOLUMNS ( 
		ProductCategory[Category],
		Dates[Year],
		NONVISUAL ( SUMMARIZE ( Sales, Dates[Year] ) ),
		TREATAS ( { "Audio" }, ProductCategory[Category] ),
		"Sales", [Sales Amount],
		"#Years in Dates", [#Years in Dates],
		"#Years in Sales", [#Years in Sales]
	)
ORDER BY 
	ProductCategory[Category] ASC,
	Dates[Year] ASC

This can also be useful when working with Window functions like OFFSET, if you write a query that returns Sales of Previous Year Partitioned by Brand under a Filter that filters only few years you will notice that First Year of each Brand returns a Blank.

DEFINE 
    MEASURE Sales[Sales PY OFFSET] =
        VAR SourceTable =
            CALCULATETABLE (
                SUMMARIZE ( Sales, Dates[Year], Products[Brand] ),
                ALLSELECTED ()
            )
        VAR PreviousYearByBrand =
            OFFSET (
                -1,
                SourceTable,
                ORDERBY ( Dates[Year] ),
                PARTITIONBY ( Products[Brand] )
            )
        RETURN
            CALCULATE ( [Sales Amount], PreviousYearByBrand )

EVALUATE
	SUMMARIZECOLUMNS (
	    Products[Brand],
	    Dates[Year],
	    TREATAS ( { "Contoso", "Fabrikam" }, Products[Brand] ),
	    TREATAS ( { 2023, 2024 }, Dates[Year] ),
	    "Sales", [Sales Amount],
	    "Sales PY", [Sales PY OFFSET]
	)
ORDER BY
    Products[Brand] ASC,
    Dates[Year] ASC

Without NONVISUAL the above code generates the following result:

If this isn't what you want, and you still want to return the sales of 2022 in 2023 instead of a blank then you can use NONVISUAL.

Complete code:

DEFINE 
    MEASURE Sales[Sales PY OFFSET] =
        VAR SourceTable =
            CALCULATETABLE (
                SUMMARIZE ( Sales, Dates[Year], Products[Brand] ),
                ALLSELECTED ()
            )
        VAR PreviousYearByBrand =
            OFFSET (
                -1,
                SourceTable,
                ORDERBY ( Dates[Year] ),
                PARTITIONBY ( Products[Brand] )
            )
        RETURN
            CALCULATE ( [Sales Amount], PreviousYearByBrand )

EVALUATE
	SUMMARIZECOLUMNS (
	    Products[Brand],
	    Dates[Year],
	    TREATAS ( { "Contoso", "Fabrikam" }, Products[Brand] ),
	    NONVISUAL ( TREATAS ( { 2023, 2024 }, Dates[Year] ) ),
	    "Sales", [Sales Amount],
	    "Sales PY", [Sales PY OFFSET]
	)
	
ORDER BY
    Products[Brand] ASC,
    Dates[Year] ASC

So how does Window function work here? Before the explanation let me reiterate when we use NONVISUAL we are instructing the engine to only filter the groupby columns (Dates[Year] in this case) and not impact the measures.

The groupby columns creates the Filter Context for the measures, and since we only show 2023 and 2024 the Filter Context at any cell has either of the year in combination with the Products[Brand].

The following image represents the Filter Context in which the measures are executed.

But the SourceTable variable in measure [Sales PY OFFSET] returns a table that has existing combination of Year and Brand. Now when the Filter Context is Products[Brand] = "Contoso" && Dates[Year] = 2023 the DAX Engine filters the SourceTable variable to identify the Current Row that will be later used to move relatively.

Once the Current Row is found the OFFSET shifts back to the previous row (Products[Brand] = "Contoso" && Dates[Year] = 2022), grabs that and returns it to CALCULATE which applies that to the Filter Context and overwrites the existing filters.

So what happens here is that when we use NONVISUAL the table returned by SourceTable variable contains all the existing combinations, but when we do not use NONVISUAL the engine returns only the pairs visible in the current Filter Context due to which when the Current Row is Contoso 2023 the engine isn't able to find any previous row hence it returns a BLANK.