Reply
Frequent Visitor
Posts: 4
Registered: ‎04-06-2018
Accepted Solution

Difference between 3 types of CALCULATE

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!


Accepted Solutions
Senior Member
Posts: 389
Registered: ‎02-29-2016

Re: Difference between 3 types of CALCULATE

[ Edited ]

Oops, you're quite right, I misread Measure 1 Smiley Happy

 

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

View solution in original post


All Replies
Senior Member
Posts: 389
Registered: ‎02-29-2016

Re: Difference between 3 types of CALCULATE

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

 

Frequent Visitor
Posts: 4
Registered: ‎04-06-2018

Re: Difference between 3 types of CALCULATE

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

Senior Member
Posts: 389
Registered: ‎02-29-2016

Re: Difference between 3 types of CALCULATE

[ Edited ]

Oops, you're quite right, I misread Measure 1 Smiley Happy

 

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

Highlighted
Frequent Visitor
Posts: 4
Registered: ‎04-06-2018

Re: Difference between 3 types of CALCULATE

Thanks for the good explanation!

 

Tom