Forum Discussion
Using ALLEXCEPT and/or ALLSELECTED to ignore multiple active slicers except for 2 slicers/columns
Memorable MemberHi,
It depends on the data model if this will work, but you can try:
Sample Measure =
VAR MaxDate = Max(EncounterFact[EncounterDate.EncounterDate])
VAR MinDate = Min(EncounterFact[EncounterDate.EncounterDate])
VAR _Filter = Filter(ALL(EncounterFact),
ALL(ModalityCategory),
ALL(ProviderType),
ALL(ServiceLineRevised)
,EncounterFact[EncounterDate.EncounterDate] > MinDate && EncounterFact[EncounterDate.EncounterDate] < MaxDate)
RETURN
CALCULATE ( EncounterFact[Providers], _Filter)stevedep , thanks for getting back to me. I tried writing the formula, but the FILTER function will only take 1 table and a FilterExpression. The additional ALL functions for ModalityCategory, ProviderType and ServiceLineRevised can't be added and they are actually columns, not tables. I only have 1 table, EncounterFact, in my data model.
Any other ideas? Seems strange that using ALL or ALLEXCEPT doesn't return the 1,414 total number of the table when multiple external slicers/filters are applied to the measure. You'd think it would ignore them, but not if multiple are applied apparently.
- stevedep
My 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.
- stevedep
What is the calculation behind
EncounterFact[Providers]
?
stevedep The single table, EncounterFact, in the data model is coming from a SSAS Tabular cube source, which is why there aren't seperate tables.
The measure:
EncounterFact[Providers] is: CALCULATE ( DISTINCTCOUNT ( 'EncounterFact'[Encounters.ProviderID] ), EncounterFact[Encounters.ProviderID] > 0 )
So the DISTINCTCOUNT of Encounters.ProviderID of the entire date range is 1,455, but between 1/1/20 - 6/7/20 it is 1,414. It seems easy to return the 1,455 of the whole table that the slicers don't impact. Somehow I need to return an expanded DAX table that is filtered with that date slicer to give the 1,414 value I want, and then use ALL or ALLEXCEPT to override the non-date slicers in my menu.