Difference between 3 types of CALCULATE

[PowerTom]

Hello,

I have a basic DAX question about CALCULATE.

We have three types of CALCULATE statements :

1. Chutes Y-1 = VAR Year = CALCULATE([CurrentYear]-1)    RETURN CALCULATE(CALCULATE([Chutes];'Date'[Jaar]=Year);ALL('Date'))

2. Chutes Y-1 TEST = VAR Year = CALCULATE([CurrentYear]-1)    RETURN CALCULATE([Chutes];'Date'[Jaar]=Year;ALL('Date'))

3. 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!

[OwenAuger]

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

[PowerTom]

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 · Accepted 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

1. Removing filters on 'Date'[Jaar] with ALL('Date'), and
2. 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

[PowerTom]

Thanks for the good explanation!

Tom