Forum Discussion
Difference between 3 types of CALCULATE
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'))
- Chutes Y-1 TEST = VAR Year = CALCULATE([CurrentYear]-1) RETURN CALCULATE([Chutes];ALL('Date');'Date'[Jaar]=Year)
Do those statements have the same behaviour or is it possible that they give different results? If so, why?
Thanks for your answer!
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
- Removing filters on 'Date'[Jaar] with ALL('Date'), and
- Adding of a filter on the same column 'Date'[Jaar]
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
4 Replies
- OwenAuger
Super User
Hi PowerTom
All 3 measures have the same behaviour.
- Measures 2 & 3 behave the same since the order of CALCULATE filter arguments (2nd argument onwards) doesn't matter.
- Measure 1 behaves the same as measures 2 & 3 since the outer CALCULATE in Measure 1 has no effect.
In the absence of a row context, an expression of the form CALCULATE ( CALCULATE ( [Expression], ...) ) has the same effect as CALCULATE ( [Expression], ... ). The outer CALCULATE includes no filter arguments, so has no effect on the result of the inner CALCULATE.
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
- OwenAuger
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
- Removing filters on 'Date'[Jaar] with ALL('Date'), and
- Adding of a filter on the same column 'Date'[Jaar]
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
