- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Difference between 3 types of CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-06-2018 01:15 AM

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!

Solved! Go to Solution.

Accepted Solutions

## Re: Difference between 3 types of CALCULATE

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-06-2018 07:22 AM - edited 04-06-2018 07:27 AM

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

All Replies

## Re: Difference between 3 types of CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-06-2018 05:48 AM

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

## Re: Difference between 3 types of CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-06-2018 06:12 AM

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

## Re: Difference between 3 types of CALCULATE

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-06-2018 07:22 AM - edited 04-06-2018 07:27 AM

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

## Re: Difference between 3 types of CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-06-2018 07:43 AM

Thanks for the good explanation!

Tom