ALLEXCEPT - How does it work?

[Jan_Trummel]

Hello guys, I have a question to the function ALLEXCEPT.

As Microsoft says, the function “removes all context filters in the table except filters that have been applied to the specified columns”.

The problem is that I see a different behavior. Ok, let me show you an example to explain it:

The example

I have this list of leaders from US and Germany with their party and the date of taking office.

List of leaders:

Now I want to create a report that shows the number of leaders for both countries.

In this report I will put two slicers: the first one sets the date range of taking office and the second filters the party.

Here is the report:

https://app.powerbi.com/view?r=eyJrIjoiMGVjMTAyYzEtZTNkMS00YzgxLTgwNTgtMmYxYmQyYmYxM2NkIiwidCI6IjUzY... 

The measures

Here is the measure that counts the number of leaders:

Number of leaders = COUNTROWS('List of leaders')

Now I create another measure with ALLEXCEPT that will show the total number of leaders. This measure should only be filtered by the date of taking office, but not by the party. Here it is:

Total number of Leaders =

CALCULATE(

    [Number of leaders],

    ALLEXCEPT('List of leaders','List of leaders'[Taking office])

)

The problem

If I change the date range in the first slicer, both measure get recalculated. For example, I change the date range to 1998 – 2020, the measure with ALLEXCEPT shows 5 leaders. That is all fine and that’s what it should do.

But if I now select a party in the second slicer, BOTH measures get recalculated again. And that is what I don’t understand. For example, I select Republicans and both measures show 2 leaders. But in my opinion, the ALLEXCEPT-measure should not get recalculated when I select a party!

And another strange thing: If I don’t change the date range in the first slicer (so 1998 – 2021) and then I select a party, the ALLEXCEPT-measure gets NOT recalculated!

So, can you help me to understand this?

With best regards!

[MFelix]

Hi @Jan_Trummel ,

This type of functions have lots of particularities. I suggest you chech the explanation on the DAX Guide and check the video with the examples. this is a very complete guide about DAX functions.

https://dax.guide/allexcept/

[Jan_Trummel]

Hello MFelix,

thank you for your answer. I will check out the guide and the video.

I will let you know, if this solved my problem.

Greetings

[v-deddai1-msft · Accepted solution]

Hi @Jan_Trummel ,

The issue will arise when you have two or more columns of the same table are filtered together. It will kicks auto-exist mechanism you can learn more about the auto-exist mechanism from https://www.sqlbi.com/articles/understanding-dax-auto-exist/

>> And another strange thing: If I don’t change the date range in the first slicer (so 1998 – 2021) and then I select a party, the ALLEXCEPT-measure gets NOT recalculated!

In this situation, there is only one filter from the slicer patry column, so it will works as expected : ALLEXCEPT has ingore the filter from the slicer.

>>But if I now select a party in the second slicer, BOTH measures get recalculated again. And that is what I don’t understand. For example, I select Republicans and both measures show 2 leaders. But in my opinion, the ALLEXCEPT-measure should not get recalculated when I select a party!

In this situation, there are two columns filter from one table. The the query executed to retrieve[Total number of leaders] looks like the following:

EVALUATE

SUMMARIZECOLUMNS (

    TREATAS ( { "Republicans" }, 'List of leaders'[Party] ),

    TREATAS ( { 1998...2020 }, 'List of leaders'[Taking offices] ),

    "Result", [Total number of leaders]

)

The table has been filtered before you calculated [Total number of leaders]. 

SUMMARIZECOLUMNS (

    TREATAS ( { "Republicans" }, 'List of leaders'[Party] ),

    TREATAS ( { 1998...2020 }, 'List of leaders'[Taking offices] ))

// Retrieving the existing values of the Party and Taking offices pair, returning only the existing ones. Then it calculated [Total number of leaders]. 

 It is  scenario where auto-exist might produce surprising results. Even seasoned DAX coders might fall into the trap of auto-exist, thinking that they are dealing with a bug when it is just auto-exist messing up the calculations.

In https://www.sqlbi.com/articles/understanding-dax-auto-exist/ it explain the strange behavior of the auto-exist

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Jan_Trummel]

Thank you for this detailed information! 👍