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 am attempting to use variables within a measure, but not getting the desired results. I've looked around at all kinds of DAX reference material and can't figure out why this doesn't work. I know there are other ways to accomplish why I'm trying to do, but I'd like to know specifically why this doesn't work.
Basically, I'm setting a variable and then trying to recalculate that variable within the same measure. The recalculation doesn't work.
Data (table name is PD):
Dt | Value |
1/1/2018 | 100 |
1/2/2018 | 200 |
1/3/2018 | 250 |
1/4/2018 | 300 |
1/5/2018 | 400 |
Dt has a relationship to a Date table (DateTab)
Creating a measure that finds the difference in Value from the previous day:
Delta = var __TodayVal = SUM(PD[Value]) var __PrevVal = CALCULATE(__TodayVal, PREVIOUSDAY(DateTab[Date])) var __Delta = __TodayVal -__PrevVal return __Delta
Here is what I would expect
Dt | Value | Delta |
1/1/2018 | 100 | |
1/2/2018 | 200 | 100 |
1/3/2018 | 250 | 50 |
1/4/2018 | 300 | 50 |
1/5/2018 | 400 | 100 |
But this is what I get
I'm sure it has something to do with evaluation context inside the measure, but I'm wracking my brain to be able to explain it. I know I can work around it by creating multiple measures, but I'd like to know a concrete reason for why this can't be done in a single measure.
Thanks,
David
Solved! Go to Solution.
A variable in DAX is actually a constant. Unlike variables in other programming languages, once you declare a variable in DAX by assigning it a value, that value is immutable. Your variable __TodayVal in the CALCULATE for __PrevVal will not be affected at all by the filter argument.
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 |
---|---|
111 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |