cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kerry_M Frequent Visitor
Frequent Visitor

Why does result change when using VAR vs MEASURE in another measure?

I am curious as to why a formula using a measure caculates differently from a formula using a VAR with the exact same definition as the measure. There must be something different about the way VAR are calculated vs a measure. Can anyone explain?

 

Here is my example:

 

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 = 
FIRSTNONBLANK(Orders[ProductName], IF([EarliestDate] = CALCULATE(Minx(ALLSELECTED(Orders),Orders[OrderDate]),VALUES(Orders[OrderDate]) ), 1, BLANK() ) )
I decided to change the measure so that it would not need the [EarliestDate] measure.
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).
Any ideas or suggestions as to why?
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()
   )
)
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Why does result change when using VAR vs MEASURE in another measure?

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
5 REPLIES 5
Super User
Super User

Re: Why does result change when using VAR vs MEASURE in another measure?

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
PaulDBrown Senior Member
Senior Member

Re: Why does result change when using VAR vs MEASURE in another measure?

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”

Kerry_M Frequent Visitor
Frequent Visitor

Re: Why does result change when using VAR vs MEASURE in another measure?

Thanks so much Matt. That worked perfectly! 

Highlighted
Super User
Super User

Re: Why does result change when using VAR vs MEASURE in another measure?

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

  1. VAR is evaluated first, before any changes are made to filter and row contexts.
  2. 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.

 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Kerry_M Frequent Visitor
Frequent Visitor

Re: Why does result change when using VAR vs MEASURE in another measure?

This is a very helpful explanation. Thank you.