Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Filter and Allexcept

Topic Options

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

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

Filter and Allexcept

10-08-2016
04:06 AM

Hello everyone,

I have classic calendar and sales tables in my model (1 to many related with the shared column "Date").

Do you have any ideas why :

[Measure1] = CALCULATE( Sum(Sales[Quantity] , FILTER(**ALLEXCEPT**(Calendar;Calendrar[Year]) , Calendar[Month] = 3))

and

[Measure2] = CALCULATE( Sum(Sales[Quantity] , FILTER(**ALL**(Calendar) , Calendar[Month] = 3))

**return the same result ?** - even when the user selects a year with a slicer (from Calendar table of course).

For example when the user selects **2014** with Calendar Slicer, I would expect [Measure1] to compute the sum of quantity for **2014** __and__ **month number 3**, whereas it computes the sum of quantity for month number 3 of all the years in the model...

It seems like ALLEXCEPT works as an ALL when it is used as a table argument in a FILTER. Anyone can explain ?

Thanks in advance 🙂

I am using Power Pivot in Excel 2016.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

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

10-09-2016
04:57 AM

Hi @Datatouille

This page contains an explanation of what you are observing:

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

To roughly quote from that page:

"An ALL(Calendar) statement is automatically applied when you apply a filter over a column of Date type that is the primary key in a relationship."

In your case, both of these tables used as SetFilter arguments for CALCULATE contain the column Calendar[Date]:

- FILTER ( ALLEXCEPT ( Calendar, Calendar[Year] ), Calendar[Month = 3 )
- FILTER ( ALL ( Calendar ), Calendar[Month = 3 )

So the DAX engine will automatically remove all filters on Calendar within the CALCULATE function when these are used as SetFilter arguments, by automatically adding ALL ( Calendar ) as a SetFilter argument.

In effect it will translate:

[Measure1] = CALCULATE ( SUM ( Sales[Quantity] ), FILTER ( ALLEXCEPT ( Calendar, Calendar[Year] ), Calendar[Month] = 3 ) ) to [Measure1] = CALCULATE ( SUM ( Sales[Quantity] ), FILTER ( ALLEXCEPT ( Calendar, Calendar[Year] ), Calendar[Month] = 3 ),ALL ( Calendar ))

and

[Measure2] = CALCULATE ( SUM ( Sales[Quantity] ), FILTER ( ALL ( Calendar ), Calendar[Month] = 3 ) ) to [Measure2] = CALCULATE ( SUM ( Sales[Quantity] ), FILTER ( ALL ( Calendar ), Calendar[Month] = 3 ),ALL ( Calendar ))

In the case of Measure1, the intended effect of ALLEXCEPT was lost because ALL ( Calendar ) was automatically applied on top.

In the case of Measure2, it didn't make any difference because you were already filtering ALL ( Calendar ) anyway.

For the effect you were looking for with Measure1 (i.e. retaining any existing filters on Calendar[Year] but setting Month = 3) you could use a measure like:

[Measure1 V2] = CALCULATE ( SUM ( Sales[Quantity] ), ALLEXCEPT ( Calendar, Calendar[Year] ), Calendar[Month] = 3 )

Using ALL or ALLEXCEPT as a top-level argument for CALCULATE (rather than within FILTER) invokes the 'remove filters' behaviour of these functions, but doesn't add the corresponding table to the filter context. This avoids triggering the automatic ALL ( Calendar ) being added.

Some discussion of this here as well:

http://mdxdax.blogspot.co.nz/2011/03/logic-behind-magic-of-dax-cross-table.html

Regards,

Owen 🙂

My Blog

Connect on Twitter

Connect on LinkedIn

7 REPLIES 7

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

10-10-2016
05:31 PM

Just some observations

Your two measures are:

[Measure1] = CALCULATE( Sum(Sales[Quantity] , FILTER(**ALLEXCEPT**(Calendar;Calendrar[Year]) , Calendar[Month] = 3))

[Measure2] = CALCULATE( Sum(Sales[Quantity] , FILTER(**ALL**(Calendar) , Calendar[Month] = 3))

Problem 1: The closing bracket to the SUM().

CALCULATE( Sum(Sales[Quantity] , ....)

is NOT same as

CALCULATE( Sum(Sales[Quantity] ), ....)

Since the format to Calculate is CALCULATE(expr, filter1, filter2,....)

Former is equivalent to CALCULATE(expr). That is

Sum(Sales[Quantity] , FILTER(**ALLEXCEPT**(Calendar;Calendrar[Year]) , Calendar[Month] = 3)) is an expression due to the position of the closing brackets.

Problem2: As already noted above, there is no need to wrap **ALLEXCEPT**(Calendar,Calendrar[Year]) in a FILTER() function. Simply use it as the Filters of your CALCULATE. That is...

[Measure1] = CALCULATE( Sum(Sales[Quantity]**)**, **ALLEXCEPT**(Calendar,Calendrar[Year]), Calendar[Month] = 3)

[Measure2] = CALCULATE( Sum(Sales[Quantity]**)**, **ALL**(Calendar) , Calendar[Month] = 3)

Don't forget to indicate vote and indicate answers.

Cheers

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

10-09-2016
04:57 AM

Hi @Datatouille

This page contains an explanation of what you are observing:

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

To roughly quote from that page:

"An ALL(Calendar) statement is automatically applied when you apply a filter over a column of Date type that is the primary key in a relationship."

In your case, both of these tables used as SetFilter arguments for CALCULATE contain the column Calendar[Date]:

- FILTER ( ALLEXCEPT ( Calendar, Calendar[Year] ), Calendar[Month = 3 )
- FILTER ( ALL ( Calendar ), Calendar[Month = 3 )

So the DAX engine will automatically remove all filters on Calendar within the CALCULATE function when these are used as SetFilter arguments, by automatically adding ALL ( Calendar ) as a SetFilter argument.

In effect it will translate:

[Measure1] = CALCULATE ( SUM ( Sales[Quantity] ), FILTER ( ALLEXCEPT ( Calendar, Calendar[Year] ), Calendar[Month] = 3 ) ) to [Measure1] = CALCULATE ( SUM ( Sales[Quantity] ), FILTER ( ALLEXCEPT ( Calendar, Calendar[Year] ), Calendar[Month] = 3 ),ALL ( Calendar ))

and

[Measure2] = CALCULATE ( SUM ( Sales[Quantity] ), FILTER ( ALL ( Calendar ), Calendar[Month] = 3 ) ) to [Measure2] = CALCULATE ( SUM ( Sales[Quantity] ), FILTER ( ALL ( Calendar ), Calendar[Month] = 3 ),ALL ( Calendar ))

In the case of Measure1, the intended effect of ALLEXCEPT was lost because ALL ( Calendar ) was automatically applied on top.

In the case of Measure2, it didn't make any difference because you were already filtering ALL ( Calendar ) anyway.

For the effect you were looking for with Measure1 (i.e. retaining any existing filters on Calendar[Year] but setting Month = 3) you could use a measure like:

[Measure1 V2] = CALCULATE ( SUM ( Sales[Quantity] ), ALLEXCEPT ( Calendar, Calendar[Year] ), Calendar[Month] = 3 )

Using ALL or ALLEXCEPT as a top-level argument for CALCULATE (rather than within FILTER) invokes the 'remove filters' behaviour of these functions, but doesn't add the corresponding table to the filter context. This avoids triggering the automatic ALL ( Calendar ) being added.

Some discussion of this here as well:

http://mdxdax.blogspot.co.nz/2011/03/logic-behind-magic-of-dax-cross-table.html

Regards,

Owen 🙂

My Blog

Connect on Twitter

Connect on LinkedIn

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

11-07-2016
02:04 PM

Hi,

Thanks for this wonderful explanation.

Sorry for not getting back to you earlier, I hadn't checked this post for a while.

I think I get your point but I still have two questions:

1. Do you know why this ALL behaviour exists in that particular context ?

2. I am applying my filter on Year and not directly on my Date column. But apparently same behaviour, even if it is indirect right ?

I made my initial question simple because in reality my "3" is dynamic - which is why I had to use a Filter.

Btw, do you know why Microsoft made it impossible to use "advanced" or "dynamic" filters in a raw calculate ?

Many thanks for your contribution and your very detailed examples.

Tristan

PS : I had correctly put the brackets in my measure, sorry for the spelling mistake in the initial post @EAfang !

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

11-09-2016
03:00 AM

Hi again Tristan!

- I believe the ALL( Calendar ) behaviour is just there for convenience, and to make the time intelligence functions easier to implement. I guess it's often the case that if you provide a filter argument containing the Calendar[Date] column, you want to clear any filters on other columns of the Calendar table at the same time. This ALL behaviour helps the time intelligence functions, since those functions just need to return a column of Date values, and any other Calendar filters are automatically cleared.
- Actually your filter argument for the CALCULATE function
FILTER ( ALLEXCEPT ( Calendar, Calendar[Year] ), Calendar[Month] = 3 )

contains all columns of the Calendar table except Year, with Month = 3,*for all years*. In other words it contains the month of March in every year, looking something like this:**Date****Month**1/03/2014 3 ⋮ ⋮ 31/03/2014 3 1/03/2015 3 ⋮ ⋮ 31/03/2015 3 1/03/2016 3 ⋮ ⋮ 31/03/2016 3

The important thing is that this table contains the Date column, so the DAX engine automatically clears all filters on Calendar within CALCULATE. Unfortunately, the effect of this is that your filter on the Year column is lost.

I would say it's a bit dangerous to use**FILTER( ALLEXCEPT ( Calendar, Calendar[Col1]), Calendar[Col2] = X )**as an argument for CALCULATE since it probably won't have the desired effect, and instead use two arguments**ALLEXCEPT ( Calendar, Calendar[Col1] ), Calendar[Col2] = X** - If your month value is dynamic, you could instead use something like this to save the value as a constant:
[Measure1 V3] = VAR FilterMonth = <any expression> RETURN CALCULATE ( SUM ( Sales[Quantity] ), ALLEXCEPT ( Calendar, Calendar[Year] ), Calendar[Month] = FilterMonth )

- When you say Microsoft made it impossible to use "advanced" or "dynamic" filters in a raw calculate, are you talking about the restriction that Boolean expressions used as arguments for CALCULATE must reference a single column, but can't reference a measure, use CALCULATE or return/scan a table?

I guess it was just a design choice to provide a shorthand just for the "simple" case of overwriting filters on a single column using constant value(s), which is presumably quite common.

Also, allowing more complex expressions within the Boolean expression would probably lead to unintended results.

Since an argument within CALCULATE like**Table[Col] = X**is translated into**FILTER ( ALL ( Table[Col] ), Table[Col] = X )**, if X is a measure or contains CALCULATE then it could have different values for each value of Table[Col], whereas the intention might have been for X to take the constant value from the outer filter context.

However, you can use VAR/RETURN to save a constant value to use within a Boolean expression, as in point 3 above 🙂

My Blog

Connect on Twitter

Connect on LinkedIn

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

01-20-2017
03:20 PM

Awesome,

Thanks a lot Owen. I hadn't checked this post for a while...

And I have improved a lot ever since 🙂 Your explanation is really clear.

Congrats & thanks again

Tristan

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

10-10-2016
03:31 AM

@OwenAuger, that's a damned good and excellently illustrated explanation of the issue and/or subject.

best regards

Oxenskiold.

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

10-08-2016
06:27 AM

You should use ALLSELECTED for the Year Slicer to filter your table correctly.

For the difference between ALL and ALLSELECTED, please refer to the very well explained blog at here.

Thanks & Regards,

Bhavesh

Love the Self Service BI.

Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Bhavesh

Love the Self Service BI.

Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Announcements

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Featured Topics

Top Solution Authors

User | Count |
---|---|

346 | |

99 | |

62 | |

51 | |

47 |

Top Kudoed Authors

User | Count |
---|---|

333 | |

122 | |

86 | |

66 | |

65 |