Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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() ) )
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() ) )
Solved! Go to Solution.
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”
Proud to be a Super User!
Paul on Linkedin.
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
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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |