ALL & ALLEXCEPT Not Respecting More than 1 Filter/Slicer Applied

[Anonymous]

Hello PBI/DAX experts!

(This is my first post so please go easy. After seeing what good and bad posts look like I was intentional about including pictures and the DAX of what I'm doing, but please let me know what else may be helpful. I've been a consumer of the PBI community for a couple years now and have benefited greatly from its content, so thank you! Maybe I can contribute with a scenario of my own that may benefit another.)

I'm having difficulty figuring out why my ALL and ALLEXCEPT measures are producing unintended results when selecting more than 1 filter/slicer on my report. Below is a "Filters Menu" that contains multiple slicers that users can choose from when interacting with the report. The visual on the right is a simple table visual to display my measures. Here is the DAX code of each from left to right and they are from 1 single table in my PBI model:

1) My numerator: Providers = DISTINCTCOUNT ( 'EncounterFact'[Encounters.ProviderID] )

2) Numerator & Denominator (same result with both denominators below): 

% Providers = EncounterFact[Providers] / EncounterFact[Providers (ALLEXCEPT)]

3) Denominator (ALL): 

Providers (ALL) =

CALCULATE (

EncounterFact[Providers],

ALL (

EncounterFact[ModalityCategory],

EncounterFact[ModalityRevised],

EncounterFact[ServiceLineRevised],

EncounterFact[RevisedDepartment],

EncounterFact[Encounters.ProviderName],

EncounterFact[Encounters.ProviderSpecialty],

EncounterFact[Encounters.ClinicianTitle],

EncounterFact[Encounters.VisitType],

EncounterFact[ProviderType]

)

)

4) Denominator (ALLEXCEPT): 

Providers (ALLEXCEPT) =

CALCULATE (

EncounterFact[Providers],

ALLEXCEPT (

EncounterFact,

EncounterFact[EncounterDate.EncounterDate],

EncounterFact[EncounterDate.WeekStartDate]

)

)

Both "Providers (ALL)" and "Providers (ALLEXCEPT)" are supposed to be doing the same thing, I just wanted to compare the two in case I was doing something incorrect with one or the other. The first picture shows the results when no filters/slicers are applied, which is correct. The second picture shows results when 1 filter is applied, which is correct. The third picture produces strange results when a second filter/slicer is applied. In the 3rd picture the "Providers" measure is correct at 183. However, both the ALL and ALLEXCEPT denominator measures are producing 1,384 when it should be 1,403. The "% Providers" should read 13.0% (183/1,403). For some reason I'm getting these unintended results when multiple slicers are applied even though I've specified to either ignore those columns in the table that are the slicers/filters in the menu (ALL), or I've called out the columns to accept (ALLEXCEPT).

Could someone please explain to me what I'm missing here? I want users to interact with these slicers and have them affect the table visual and those measures, just want most of them to ignore the denominator and not produce strange results if more than 1 slicer is applied.

Thanks so much!

Before:

Before

After 1 Filter/Slicer Applied

After 1 Filter/Slicer Applied

After 2 Filters/Slicers Applied

After 2 Filters/Slicers Applied

[Anonymous]

By the way... You have to show the model to us for us to be able to exactly pinpoint "the issue."

Best
D

[Anonymous]

@Anonymous, thanks so much for your feedback. I have a decent understanding of the difference you are describing between ALL and ALLEXCEPT if I were to have more than 1 table in my datamodel. In my case it's an extremely simple model, in that I have just 1 table in it: EncounterFact.

I did notice that if I remove my column EncounterFact.EncounterDate from the "Filters" pane that is being used under "Filters on all pages" that the ALL and ALLEXCEPT measures correct themselves and no longer show an unintended result. Now I'm able to use multiple visual slicers together in various combinations and it won't change it. But if I try to change the date range slicer that is using EncounterFact.EncounterDate, then I get those unintended results again.

I think somehow my measure needs to preserve that EncounterFact.EncounterDate column used in my "Filters" pane AFTER the DAX measure calculates so it reapplies that date filter back on the measure. This way it persists regardless of what combination of filters the user selects on the Filters Menu UNLESS they specifically change the date range on the "Visit Date" slicer, which is using the EncounterFact.EncounterDate column. Here is a picture below for a visual. Again, I'd like to keep the applied filter on the "Filters" pane and only override when a user changes that date range, all while ignoring the other slicers/columns used in the "Filters Menu."

Does this make more sense of what I'm trying to accomplish? Please let me know if you need anything else. I really appreciate your help!

[amitchandak]

@Anonymous , refer if these can help

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

[Anonymous]

@amitchandak, thanks so much for the response. I tried these solutions, but they don't appear to address my issue. I'm still getting unintended results. Any other ideas?

[Anonymous]

What you get from these 2 ALL's depends on how your model has been structured. The ALL version only removes direct filters on the specified columns. The ALLEXCEPT version removes all filters from the EXPANDED TABLE (EncounterFact) leaving only those that are directly applied to EncounterDate and WeekStartDate in place, IF THERE ARE ANY PRESENT. The semantics are very different. And most people do not understand this difference.

If you have any dimensions attached to the fact table, the ALL version will not remove any filters from them, whereas ALLEXCEPT will. Every time you see somewhere a reference to a TABLE (not a column of a table), it means THE EXPANDED VERSION OF THE TABLE.

Best
D