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.
Hello,
I have a basic DAX question about CALCULATE.
We have three types of CALCULATE statements :
Chutes Y-1 = VAR Year = CALCULATE([CurrentYear]-1) RETURN CALCULATE(CALCULATE([Chutes];'Date'[Jaar]=Year);ALL('Date'))
Chutes Y-1 TEST = VAR Year = CALCULATE([CurrentYear]-1) RETURN CALCULATE([Chutes];'Date'[Jaar]=Year;ALL('Date'))
Do those statements have the same behaviour or is it possible that they give different results? If so, why?
Thanks for your answer!
Solved! Go to Solution.
Oops, you're quite right, I misread Measure 1 🙂
The answer is the same though: Measure 1 behaves the same as measures 2 & 3.
Measure 1 applies the two filters in sequence, while Measures 2 & 3 apply the filters simultaneously, which is why one might expect the results could be different.
To explain:
Measure 1 first removes all 'Date' filters then adds a filter on 'Date'[Jaar], with the result being no filters on 'Date' except for the filter on 'Date'[Jaar].
Measure 2 simultaneously removes all 'Date' filters and adds a filter on 'Date'[Jaar], which results in the intersection of the two filters.
In DAX, the intersection of
is defined to be just the filter specified in (2), i.e. a filter on 'Date'[Jaar] with all other 'Date' filters removed, which is the same as in Measure 1.
This makes sense, since the removal of a filter is conceptually the same as setting the filter to all possible values in the column, and intersecting this with a particular filter just results in the particular filter being applied.
In general, filters applied in sequence with nested CALCULATEs won't necessarily be the same as filters applied simultaneously within a single CALCULATE. It just happens to work this way when the outer filter is an ALL(...) function.
Regards,
Owen
Hi @PowerTom
All 3 measures have the same behaviour.
Regards,
Owen
Hello Owen,
Thank you for your answer.
It is clear for me now that measures 2 & 3 behave the same, but I think you overlooked something for measure 1.
Measure 1 does have a filter argument in the outer CALCULATE :
Chutes Y-1 = VAR Year = CALCULATE([CurrentYear]-1) RETURN CALCULATE(CALCULATE([Chutes];'Date'[Jaar]=Year);ALL('Date'))
So with keeping this in mind, does measure 1 behaves the same as mesaures 2 & 3?
Thanks,
Tom
Oops, you're quite right, I misread Measure 1 🙂
The answer is the same though: Measure 1 behaves the same as measures 2 & 3.
Measure 1 applies the two filters in sequence, while Measures 2 & 3 apply the filters simultaneously, which is why one might expect the results could be different.
To explain:
Measure 1 first removes all 'Date' filters then adds a filter on 'Date'[Jaar], with the result being no filters on 'Date' except for the filter on 'Date'[Jaar].
Measure 2 simultaneously removes all 'Date' filters and adds a filter on 'Date'[Jaar], which results in the intersection of the two filters.
In DAX, the intersection of
is defined to be just the filter specified in (2), i.e. a filter on 'Date'[Jaar] with all other 'Date' filters removed, which is the same as in Measure 1.
This makes sense, since the removal of a filter is conceptually the same as setting the filter to all possible values in the column, and intersecting this with a particular filter just results in the particular filter being applied.
In general, filters applied in sequence with nested CALCULATEs won't necessarily be the same as filters applied simultaneously within a single CALCULATE. It just happens to work this way when the outer filter is an ALL(...) function.
Regards,
Owen
Thanks for the good explanation!
Tom
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |