Forum Discussion
Using ALLEXCEPT and/or ALLSELECTED to ignore multiple active slicers except for 2 slicers/columns
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)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.- stevedep
That's helpful, in that case it's probably;
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 &&
EncounterFact[Encounters.ProviderID] > 0
)
RETURN CALCULATE ( distinctcount( EncounterFact[Encounters.ProviderID])
, _Filter)Hey Steve, unfortunately that didn't work either. I actually started to go down a seperate path using some variables and SWITCH. I probably should post this as a new post on the forum, but thought you might look at it and see if it works better. Below is a screenshot of the Excel spreadsheet showing 2 examples. The first example contains my sample data and slicer visuals available to select from. In the first example you can see all 3 slicers are active with a selection. The second example shows what should happen if 1 slicer is selected. I've added some notes with the examples, but I want to find the DAX that would work for BOTH the yellow ? boxes.
Here is some of the DAX that I started for the measure. Have any ideas on this to make it work? Thanks so much again for your help.
Sample Measure = // Measure 1: VAR ProviderID = CALCULATE ( DISTINCTCOUNT ( EncounterFact[Encounters.ProviderID] ), EncounterFact[Encounters.ProviderID] > 0 ) // Hierarchy levels in order of smallest (most granular) to largest: VAR VisitTypeSelected = ISFILTERED ( EncounterFact[Encounters.VisitType] ) VAR ChannelSelected = ISFILTERED ( EncounterFact[ModalityRevised] ) VAR CategorySelected = ISFILTERED ( EncounterFact[ModalityCategory] ) // Denominator measures based on slicer selection: VAR VisitTypeTotal = CALCULATE ( ProviderID, ALL ( EncounterFact[Encounters.VisitType] ) ) VAR ChannelTotal = CALCULATE ( ProviderID, ALL ( EncounterFact[ModalityRevised] ) ) VAR CategoryTotal = CALCULATE ( ProviderID, ALL ( EncounterFact ) ) RETURN SWITCH ( TRUE (), VisitTypeSelected, VisitTypeTotal, ChannelSelected, ChannelTotal, CategorySelected, CategoryTotal, CategoryTotal )