# Switching Dimensions Based on Slicer Selection

Mis à jour : 8 août 2020

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:

One dimension slicer "Device Type" in the Y axes

One dimension slicer "Date" in the X axes

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 belowYou 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__