Forum Discussion
Why does result change when using VAR vs MEASURE in another measure?
I created a measure to show the first item a person ordered. It works!
This measure used another measure called [Earliest Date] as part of the calculation
( [EarliestDate] = Min(Orders[OrderDate]) )
Name of First Product using a measure =I decided to change the measure so that it would not need the [EarliestDate] measure.
FIRSTNONBLANK(Orders[ProductName], IF([EarliestDate] = CALCULATE(Minx(ALLSELECTED(Orders),Orders[OrderDate]),VALUES(Orders[OrderDate]) ), 1, BLANK() ) )
I modified it to use a VAR instead (with the exact same definition as EarliestDate). Now the calculation doesn't produce the same results (and the new results are wrong...instead of the earliest product purchased the formula returns the first product alphabetically for the customer).
Name of First Product using a Var =
Var EarliestDateOfPurchase = Min(Orders[OrderDate])
Return
FIRSTNONBLANK(Orders[ProductName],
IF(EarliestDateOfPurchase =
CALCULATE(
Minx(ALLSELECTED(Orders),Orders[OrderDate]),
VALUES(Orders[OrderDate])
),
1,
BLANK()
)
)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
5 Replies
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
Thanks so much Matt. That worked perfectly!
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”
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.
This is a very helpful explanation. Thank you.
