Forum Discussion
Using ALLEXCEPT and/or ALLSELECTED to ignore multiple active slicers except for 2 slicers/columns
Hello, I have a report that contains 1 table in the data model: "EncounterFact". It has 2 measures: 1) Providers (ALLEXCEPT 2) = CALCULATE ( CALCULATE (EncounterFact[Providers], ALLEXCEPT...
stevedep
Memorable Member
Memorable MemberMy bad, probably it could be:
difficult to test without the model and detailed sample data, but lets see:
Sample Measure =
VAR MaxDate = Max(EncounterFact[EncounterDate.EncounterDate])
VAR MinDate = Min(EncounterFact[EncounterDate.EncounterDate])
VAR _Filter = Filter(ALL(EncounterFact)
,EncounterFact[EncounterDate.EncounterDate] > MinDate && EncounterFact[EncounterDate.EncounterDate] < MaxDate)
RETURN
CALCULATE ( EncounterFact[Providers], ALL(ModalityCategory),
ALL(ProviderType),
ALL(ServiceLineRevised), _Filter)stevedep I tried this revised one and I get the results below in the Matrix photo. Somehow the total needs to be at the row level, but I get these various row totals. Unfortunately I can't share many details with the data since it's healthcare, but I attached a second photo showing the single table I have in the data model.
I appreciate your continued help.
Matrix
Data Model
- stevedep
The best way to get desired result is to create dimension table and a date table and then join these with your fact table. Most issues on the forum have as root cause a sub optimal data model.