Unexpected behaviour of ALLEXCEPT

[DataSkills]

Hi all, 

I have a measure as follows:

Sales ALLEXCEPT = CALCULATE(sum('Reseller Sales'[SalesAmount]), ALLEXCEPT(Reseller, Reseller[ResellerName]))

My understanding of this is that it will sum up the 'Reseller Sales'[SalesAmount], ignoring ALL filters except those that come from Reseller[ResellerName]. So if I have a slicer for Year and for say Country, these will have no impact on the result, but if I put in a slicer for Reseller Name, this WOULD have an impact. 

However, this is not what I am seeing. Adding a year slicer (from Calendar[Date], joined to 'Reseller Sales'[Order Date]) affects the result. But the slicer for Country (from Geography[Country] to 'Reseller Sales'[Country]) has no impact. I am getting confused about this behaviour.  Please tell me what I am missing about the behaviour of ALLEXCEPT! Thank you in advance!

[ValtteriN · Accepted solution]

Hi,

ALLEXCEPT removes the filters from the table in question, but not from related tables. If a filter affects Reseller Sales it will have an effect to you end result. 

Example:

Measure 18 = CALCULATE(SUM('Table (24)'[Column1]),ALLEXCEPT('Calendar','Calendar'[Date]))

Measure 19 = CALCULATE(SUM('Table (24)'[Column1]),ALL('Table (24)'),KEEPFILTERS('Calendar'))


My example uses 3 slicers. From left to right: table (24)[date], calendar[date] and c2[date].

Since all the slicers affect the base table (24) measure 18 value changes in all scenarios. Measure 19 only changes when calendar[date] is affected. 

I recommend using something similar to my measure 19.  The underlying reason for this behaviour is that your fact table is still affected by filters. Consider how the table visual is affected in my example.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/