Hierarchy Totals & Multiple Slicers using SWITCH/ALL

[Anonymous]

Hello,

I have a simple data model with 1 table (EncounterFact) in PBI. Below is a screenshot of the Excel spreadsheet showing 2 examples of what I'm trying to figure out. 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. I'm hoping some gaps just need to be filled in, but if there is a better way to go about this, please let me know. Thanks!

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
    )

[MattAllington]

Well,  believe it or not, a single flat table is not a "simple data model". You would be well advised to create a star schema with dimensions for all of the columns you are trying to filter and modify (with CALCULATE). Most problems normally then go away. 

[Anonymous]

@MattAllington Thanks so much for the feedback. This table is coming from a single source, an SSAS Tabular cube, which contains this heirarchy and dates.

Do you know how I might be able to achieve what I'm trying to do? Am I on the right track with the variables and SWITCH, or should I completely go down a different path? I've been stuck on a solution for this for weeks. Thanks again for your help!

[lbendlin]

The SSAS cube should already provide all the required dimensions and measures. How does your data source definition look like?

[stevedep]

Hi,

This should meet your requirement:

Measure 3 = 
var __Channel = SELECTEDVALUE('Table'[Channel])
var __VisitType = SELECTEDVALUE('Table'[VisityType])
var __Category = SELECTEDVALUE('Table'[Category])
return
SWITCH(TRUE();
    ISFILTERED('Table'[VisityType]);    DIVIDE(DISTINCTCOUNT('Table'[ID]);
                                                            IF(COUNTROWS(FILTERS('Table'[Channel]))=1;            
                                                                CALCULATE(DISTINCTCOUNT('Table'[ID]);ALL('Table');'Table'[Channel]=__Channel);
                                                                CALCULATE(DISTINCTCOUNT('Table'[ID]);ALL('Table'))
                                                                )
                                                )                                                ;
    ISFILTERED('Table'[Channel]);       DIVIDE(DISTINCTCOUNT('Table'[ID]);
                                                            IF(COUNTROWS(FILTERS('Table'[Category]))=1;
                                                                CALCULATE(DISTINCTCOUNT('Table'[ID]);ALL('Table');'Table'[Category]=__Category);
                                                                CALCULATE(DISTINCTCOUNT('Table'[ID]);ALL('Table'))
                                                            )
                                                )
;
    ISFILTERED('Table'[Category]);      DIVIDE(DISTINCTCOUNT('Table'[ID]);CALCULATE(DISTINCTCOUNT('Table'[ID]);ALL('Table')))
    )

As can be seen here: 

File is available here. 

Please mark as solution if this works for you. Thumbs up for the effort is appreciated.

Kind regards, Steve. 

[stevedep]

@Anonymous , Hope this works for you?