ALLEXCEPT and Page/Report Level Filters

[YoungLearning]

I want to further understand the interaction of filters with the ALLEXCEPT DAX Function.

Originally:

Adding Regional filters: North West and London:

On top of the Regional filters, add Date filter to 25th of May 

What is happening here, is that when the region filter is applied, the ALLEXCEPT(region) is impacted (summing up London and Northwest data) while the ALLEXCEPT(date) remains unaffected. Upon adding another filter on the date, 25th of May which corresponds to a London datapoint, will lead to the ALLEXCEPT(region) calculation to be narrowed down to just London.
My point here is, page filters do affect ALLEXCEPT function to some extend, such as cases like this, and I wanted to know if this is an expected behavior?

[rajendraongole1 · Accepted solution]

Hi @YoungLearning -ALLEXCEPT only removes filters from columns not specified in its arguments. Therefore, when you apply additional filters (like the date), the calculation reflects the combined effect of the filters in the filter context, narrowing down the result to only those data points that satisfy both the region and date filters.

The reason you're seeing this behavior is due to how filter context is layered and how ALLEXCEPT interacts with that context:Layering of Filters: Filters are layered on top of one another, and ALLEXCEPT does not override existing filters on the columns it does not mention. This means that if you apply a filter on Date, ALLEXCEPT('Sales', 'Region') will still respect that filter because it's not designed to remove filters on Date. When multiple filters are applied (e.g., Region and Date), the data returned is the intersection of these filters. If ALLEXCEPT is used on Region, it will still respect the Date filter because it doesn't remove it, leading to a narrowed down result set when both filters intersect.

Hope the above information helps, if you can share pbix file with sample data will check it for analyse

[YoungLearning]

Thank you for this. I guess in my use case, it would be better to have one filter enabled at a time. 

[rajendraongole1]

Nop, Happy to share, Yes it is .