DAX Variables and IF/SWITCH Statements

Earlier this week, a client came to me with a DAX error that I’d not seen before…

Query (12, 4) Calculation error in measure <insert measure name>: The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

After reviewing their code and data model, I was able to reproduce the error using Adventure Works with the following DAX code…

DEFINE MEASURE ‘Internet Sales'[TEST] = VAR test = IF ( 1 = 1, CALCULATETABLE ( VALUES ( ‘Product'[ProductKey] ), ‘Product'[Color] = “Red” ), CALCULATETABLE ( VALUES ( ‘Product'[ProductKey] ), ‘Product'[Color] = “Blue” ) ) RETURN CALCULATE ( SUM ( ‘Internet Sales'[SalesAmount] ), test ) EVALUATE ( ADDCOLUMNS ( VALUES ( ‘Date'[CalendarYear] ), “TEST”, [TEST] ) )

This is obviously a meaningless calculation, but it highlights my (previously) incorrect understanding of variables and the IF-function.

DAX variables can contain either a “scalar” value or a “table” value.

Here’s an example showing the use of a variable that contains a “scalar” value…

DEFINE MEASURE ‘Internet Sales'[Sales] = SUM ( ‘Internet Sales'[SalesAmount] ) MEASURE ‘Internet Sales'[Total Sales] = CALCULATE ( [Sales], ALL () ) MEASURE ‘Internet Sales'[Pct of Total Sales] = VAR __sales = [Sales] VAR __total_sales = [Total Sales] RETURN DIVIDE ( __sales, __total_sales ) EVALUATE ( ADDCOLUMNS ( VALUES ( ‘Date'[CalendarYear] ), “[Sales]”, [Sales], “[Total Sales]”, [Total Sales], “[Pct of Total Sales]”, [Pct of Total Sales] ) )

And here’s an example that shows the use of a variable containing a “table” value…

DEFINE MEASURE ‘Internet Sales'[Sales – Red Products] = VAR __ds_red_products = FILTER ( ‘Product’, ‘Product'[Color] = “Red” ) RETURN CALCULATE ( SUM ( ‘Internet Sales'[SalesAmount] ), __ds_red_products ) EVALUATE ( ADDCOLUMNS ( VALUES ( ‘Date'[CalendarYear] ), “[Sales – Red Products]”, [Sales – Red Products] ) )

Now, with that in mind, let’s go back to the original example…

VAR test = IF ( 1 = 1, CALCULATETABLE ( VALUES ( ‘Product'[ProductKey] ), ‘Product'[Color] = “Red” ), CALCULATETABLE ( VALUES ( ‘Product'[ProductKey] ), ‘Product'[Color] = “Blue” ) )

Would you expect this variable to contain a “table” or “scalar” value?

My first instinct is that this variable contains a “table” value because both the true and false branches return table expressions. Not only that, but the return the same table.

Unfortunately, that assumption is incorrect. The variable contains a “scalar” value. The reason is that IF is a scalar function and therefore the result is always be a scalar value. The same is true for SWITCH which is really just syntax-sugar for nested IF functions.

Note: This is way more clear when reviewing the dax.guide documentation (not so clear with official microsoft documentation).

In other words, the original calculation is rough equivalent of the following simplified example…

DEFINE MEASURE ‘Internet Sales'[TEST] = VAR test = 1 RETURN CALCULATE ( SUM ( ‘Internet Sales'[SalesAmount] ), test ) EVALUATE ( ADDCOLUMNS ( VALUES ( ‘Date'[CalendarYear] ), “TEST”, [TEST] ) )

…which in fact produces the same error message.

Big thank you to Marco Russo (b | t) for the clarification!