Switching Dimensions Based on Slicer Selection

Most of the articles on Dimension Slicers will give you a solution that will work but is not optimized once a certain volume of data is reached.

The goal of this article is to show you how to create :

• A slicer of dimension without bi-directional filters with disconnected tables

• 2 dimension slicers working in parallel, one embedded in the other

• 1 measure slicer embedding the 2 dimensions (The date + all the other dimensions)

The result is a very dynamic visual that can change each of its measures, each of its dimensions included an extra third dimension slicer in the legend.

Final Result

You will find in one single visual:

1. One dimension slicer "Device Type" in the Y axes

2. One dimension slicer "Date" in the X axes

3. One measure slicer in the Value area

Let's start by building a Power BI data model with 3 disconnected tables

First create a calculated table for the first Dimension Slicer

In the first DAX formula below, we create:

• One column "Dimension Slicer" for the value that will appear in our Dimension slicer. This value has generally the name of the column.

• One column "Dimension Slicer Value" for the values that will appear in our final measure. These values are the ones inside the column.

• The Union () regroups all columns together.

• The Selectcolumns () creates 2 columns: "Dimension Slicer" will have as value the name of the column from the selected dataset. "Dimension Slicer Value" will have as value any attributes of the column

• We repeat the SelectColumns () for each column we want to include in our dimension slicer.

Dim_Slicer = UNION(SELECTCOLUMNS(Dim_Site;"Dimension slicer";"Aircraft Family";/*attribute value"*/ "DimensionSlicerValue";Dim_Site[Aircraft Family Code]);SELECTCOLUMNS(Dim_Organisation;"Dimension slicer";"Airline";/*attribute value"*/ "DimensionSlicerValue";Dim_Organisation[organizationName]); SELECTCOLUMNS(Dim_Site;"Dimension slicer";"Tail Number";/*attribute value"*/ "DimensionSlicerValue";Dim_Site[Aircraft Tail Number]); SELECTCOLUMNS('Dim_Internet Product';"Dimension slicer";"Internet Product Version"; "DimensionSlicerValue";'Dim_Internet Product'[Internet Product Version]);SELECTCOLUMNS(Dim_Site;"Dimension slicer";"Satellite Service"; "DimensionSlicerValue";Dim_Site[Satellite Service]);SELECTCOLUMNS('Dim_Site Platform';"Dimension slicer";"Platform"; "DimensionSlicerValue";'Dim_Site Platform'[crmSitePlatform]);SELECTCOLUMNS('Dim_Target_Airport';"Dimension slicer";"Departure Airport"; "DimensionSlicerValue";'Dim_Target_Airport'[TargetAirport_IATA_Code]);SELECTCOLUMNS('Dim_Source_Airport';"Dimension slicer";"Arrival Airport"; "DimensionSlicerValue";'Dim_Source_Airport'[SourceAirport_ID]);SELECTCOLUMNS('Dim_Fleet';"Dimension slicer";"Fleet"; "DimensionSlicerValue";Dim_Fleet[Fleet]);SELECTCOLUMNS('Dim_User Agent (Device Type)';"Dimension slicer";"Device Type"; "DimensionSlicerValue";'Dim_User Agent (Device Type)'[Device Type]); SELECTCOLUMNS('Dim_User Agent (Device Type)';"Dimension slicer";"Operating System"; "DimensionSlicerValue";'Dim_User Agent (Device Type)'[Operating System]);SELECTCOLUMNS('Dim_User Agent (Device Type)';"Dimension slicer";"Internet Browser"; "DimensionSlicerValue";'Dim_User Agent (Device Type)'[Internet Browser]);SELECTCOLUMNS(Dim_Time;"Dimension slicer";"Month"; "DimensionSlicerValue";Dim_Time[yearMonth]);SELECTCOLUMNS('Fact_Internet Purchase';"Dimension slicer";"Refund reason"; "DimensionSlicerValue";'Fact_Internet Purchase'[refundReason]))

A closer look at the final Dimension Slicer Table:

Second create a calculated table for the Date Dimension Slicer

• The DAX formula is exactly the same than the one above

• So no explanation needed

Dim_slicer_date  = UNION(
SELECTCOLUMNS(Dim_Time;"Dimension slicer";"Day";/*attribute value"*/ "DimensionSlicerValue";'Dim_Time'[timeID])
;SELECTCOLUMNS(Dim_Time;"Dimension slicer";"Month";/*attribute value"*/ "DimensionSlicerValue";'Dim_Time'[yearMonth]);SELECTCOLUMNS(Dim_Time;"Dimension slicer";"Week";/*attribute value"*/ "DimensionSlicerValue";'Dim_Time'[yearWeek]
)) 

A closer look at the final Date Dimension Slicer table:

Third, disconnected table in order create the Measure Slicer:

• Here we create a native table from Power BI and we include the values we want to show in our Measure Slicer and their ID.

Step n* 4 We create one calculated measure to make the measure slicer work

• We use the Switch () and the SelectedValue () inside a variable to make it work

• If you need more information on how to build a measure slicer, I recommend one of my favorite blog: Fourmoo.com/MeasureSlicer

Variable

[Measure Slicer] = 
VAR MySelection =
SELECTEDVALUE ( 'Measure Table'[Measure];0 )
RETURN
SWITCH (
TRUE ();

Core

MySelection = "Total amount (USD)";[Total amount - USD] ;MySelection = "Number of sites w. purchases";[Number of sites with purchases] ;MySelection = "Number of PAX adoption";[Pax count]; 
MySelection = "Internet total volume MiB";[Internet total volume - MiB];MySelection = "Internet purchases";[# of Internet purchases];MySelection = "0 MB purchases";[0 MB purchase];MySelection = "Low MB purchases";[Low MB purchase];MySelection = "Average Internet volume MiB per flight";[Average of Internet volume MiB per flight];MySelection = "Internet refund amount USD";[Internet refund amount USD];MySelection = "Internet refund amount ratio";[Internet refund amount ratio]

Step n* 5 We create one calculated measure to make the dimension slicer values work

• Be careful: This is THE measure that will be used in our visual

• Here we declare in the variable the values of the Dimension Slicer [DimensionSlicerValue]

• The DAX is slightly different that in step n* 4 as we use the switch (), the calculate () and the filter () functions to filter through the Dimension Slicer that is declared in the variable below

• You can see that we embed the first calculated measure [Measure Slicer], from step n*4 in our calculate () function

Dimension/measure value = 
VAR attributselected = LASTNONBLANK('Dim_slicer'[Dimension slicer];1)
VAR axeDim = LASTNONBLANK('Dim_slicer'[DimensionSlicerValue];1)
return

SWITCH(attributselected;
"Aircraft Family";CALCULATE([Measure Slicer];FILTER(Dim_Site;Dim_Site[Aircraft Family Code]=axeDim));
"Tail Number";CALCULATE([Measure Slicer];FILTER(Dim_Site;Dim_Site[Aircraft Tail Number]=axeDim)); "Satellite Service";CALCULATE([Measure Slicer];FILTER(Dim_Site;Dim_Site[Satellite Service]=axeDim)); "Refund reason";CALCULATE([Measure Slicer];FILTER('Fact_Internet Purchase';'Fact_Internet Purchase'[refundReason]=axeDim)); "Airline";CALCULATE([Measure Slicer];FILTER(Dim_Organisation;Dim_Organisation[organizationName]=axeDim)); "Internet Product Version";CALCULATE([Measure Slicer];FILTER('Dim_Internet Product';'Dim_Internet Product'[Internet Product Version]=axeDim)); "Platform";CALCULATE([Measure Slicer];FILTER('Dim_Site Platform';'Dim_Site Platform'[crmSitePlatform]=axeDim)); "Departure Airport"));0) 

Step n* 6 - FINAL We create one calculated measure to make the date dimension slicer values work

• Here we declare in the variable the values of Date Dimension Slicer [DimensionSlicerValue] - Same process than in step n* 5

• We use the switch (), the calculate () and the filter () functions to filter through the Date Dimension Slicer that is declared in the variable- Same process than in step n* 5

• You can see that we embed the second calculated measure [Dimension/measure slicer], from step n*5 in our calculate ()

Date/Dimension/Measure Value= 
VAR attributselected = LASTNONBLANK('Dim_slicer_date '[Dimension slicer];1)
VAR axeDim = LASTNONBLANK('Dim_slicer_date'[DimensionSlicerValue];1)
return
SWITCH(attributselected;
"Month";CALCULATE([Dimension/measure slicer];FILTER(Dim_Time;Dim_Time[yearMonth]=axeDim));
"Day";CALCULATE([Dimension/measure slicer];FILTER(Dim_Time;Dim_Time[timeID]=axeDim)); "Week"; CALCULATE([Dimension/measure slicer];FILTER(Dim_Time;Dim_Time[yearWeek]=axeDim));0
) 

To sum up the operations you need to create:

Three tables

• 2 calculated tables

• 1 native Power BI table

Three calculated measures:

• A first calculated measure for the Measure Slicer Values

• A second calculated measure for the Dimension Slicer Values embedding the first calculated measure

• A third calculated measure for the Dimension & Date Slicer Values embedding the second calculated measure

Solution contributors: Maxime Gouffe & Annabelle Phillips