topic Re: Hierarchy Totals & Multiple Slicers using SWITCH/ALL in DAX Commands and Tips

Hierarchy Totals & Multiple Slicers using SWITCH/ALL

Anonymous — Sat, 13 Jun 2020 01:53:35 GMT

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 )

Re: Hierarchy Totals & Multiple Slicers using SWITCH/ALL

MattAllington — Sat, 13 Jun 2020 02:22:27 GMT

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.

Re: Hierarchy Totals & Multiple Slicers using SWITCH/ALL

Anonymous — Sat, 13 Jun 2020 12:18:29 GMT

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!

Re: Hierarchy Totals & Multiple Slicers using SWITCH/ALL

lbendlin — Sat, 13 Jun 2020 23:01:44 GMT

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

Re: Hierarchy Totals & Multiple Slicers using SWITCH/ALL

stevedep — Sun, 14 Jun 2020 07:34:00 GMT

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.

Re: Hierarchy Totals & Multiple Slicers using SWITCH/ALL

stevedep — Thu, 18 Jun 2020 15:31:12 GMT

Anonymous , Hope this works for you?