cancel
Showing results for
Did you mean:
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

## 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

## 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.
Super User I

## 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”

If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!
Thank you!

Proud to be a Super User!

Frequent Visitor

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

Thanks so much Matt. That worked perfectly!

## 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.
Frequent Visitor

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

This is a very helpful explanation. Thank you.

Announcements

#### Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors