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…
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…
And here’s an example that shows the use of a variable containing a “table” value…
Now, with that in mind, let’s go back to the original example…
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…
…which in fact produces the same error message.
3 replies on “DAX Variables and IF/SWITCH Statements”
Hi ts very interesting is this something I can do ?
DEFINE MEASURE msrFC3_V2 =
VAR FC3_True =
Thanks a lot ,you blog is very useful
aside from the minor syntax errors (extra closing parenthesis and a rogue ‘3’ in the first argument of the IF-function)… yes, that is a valid dax expression… FC3_True would contain a scalar value