Forum Discussion
Why does result change when using VAR vs MEASURE in another measure?
- 7 years ago
My guess is context transition caused by the hidden calculate inside a measure.
Try wrapping the var code min(order[orderdate]) in a calculate, as calculate(min(order[orderdate]))
context transition is a big and complex topic
I keep seeing this to the point that I no longer feel confident about using VAR in measures. Especially since I normally end up writing independent measures to check results...
Dax is in itself complex and challenging.
It would be really useful to be able to read up on the caveats on using VAR, along the lines of “do’s and dont’s”
- MattAllington7 years ago
Community Champion
Yes, DAX is complex under the hood, but you shouldn't lose confidence using VAR. VAR actually simplifies DAX a lot. All you need to know is
- VAR is evaluated first, before any changes are made to filter and row contexts.
- A measure has a hidden CALCULATE, so if you are using VAR instead of a Measure, you need to wrap the raw formula inside a CALCULATE if you want to guarantee the same result
Here is an example of a formula where VAR makes it easier.
Consider the following calculated column.
Rank Customer by Age = COUNTROWS ( FILTER ( Customer, Customer[Birth Date] < EARLIER ( Customer[Birth Date] ) ) )This formula has 2 row contexts, one from the calculated column and one from FILTER. In order to make it work, you have to use the EARLIER function to refer to the outer row context. Instead, you could use VAR here
Rank Customer = VAR ThisCustomersAge = Customer[Birth Date] RETURN COUNTROWS ( FILTER ( Customer, Customer[Birth Date] < ThisCustomersAge ) )VAR gets evaluated first. It assigns the customer's birthdate for each row in the customer table to the variable, then it filters the customer table to see how many customers are older.
- Kerry_M7 years ago
Helper II
This is a very helpful explanation. Thank you.