Switching Dimensions Based on Slicer Selection

by ThirdI Frequent Visitor on ‎05-16-2018 04:49 AM - last edited on ‎05-22-2018 10:00 AM by MindyJ Frequent Visitor

I recently came across a customer who was looking for a way to switch the dimensions dynamically using a slicer.  In this blog, I’ll be sharing how I could do it in Power BI. This can be understood using the following example:

 

Switching the dimensions of a bar chart between Daily and Monthly based on the slicer selection.

 

Let’s consider this simple model:

 

1.png

 

Its expected output is:

  

2.png

 

3.png

From the above output, we can understand that selecting Daily on the slicer displays data for the last 7 days and selecting Monthly displays data for the last 12 months.

 

However, at present, the direct implementation of the above is not possible in Power BI.

 

Here are 2 approaches with which I could achieve it:

 

  1. Using bookmarks
  2. Making a change in the model

Although using bookmarks is an easy task, we recommend using 'making a change in the model' approach. I suggest this because with Bookmarks you will have to create 2 different views - one for Daily and one for Monthly. Here you will be only be toggling between the 2 views and it won't be actually switching the dimensions dynamically.

 

Let’s explore how making a change in the model can be achieved.

To begin with, I created a lookup table with values for date/month as part of a single column. In this table, a single date appears twice – once for Daily and once for Monthly.

 

The structure of the table is as below:

 

4.png

Below are the table attributes:

 

  • Date_Key will have the date as an integer – there will be 2 records for the same date (for daily and monthly)
  • Time_Granularity will have 2 values – Daily, Monthly
  • Time_Value will contain the actual date/month values that will be displayed on the X-axis of the visual.
  • Sort_Order will have a unique value for each record (to sort the visual).
  • Time_Granularity_Order will have 1 value for each Time_Granularity.

After importing this table into Power BI, I created a slicer based on the Time_Granularity column (with Forced Selection turned On).

 

5.png

 

 

As there exists a many-to-many relationship between Fact_Sales and Date_Hierarchy table, the cross-filter direction is set to Both directions.

Then I created 2 measures to calculate the daily and monthly sales respectively.

  1. Measure to calculate the sales for the last 7 days:

    Sales_Day =
    CALCULATE(SUM('Fact_Sales'[Gross_Sales]),FILTER('Fact_Sales','Fact_Sales'[Date]>TODAY()-7&&'Fact_Sales'[Date]<=TODAY()))

  2. Measure to calculate the sales for the last 12 months:

    Sales_Month =
    var m= IF(VALUE(FORMAT(TODAY(),"mm"))=12,CONCATENATE(YEAR(TODAY()),01),CONCATENATE(YEAR(TODAY())-1,FORMAT(MONTH(TODAY())+1,"00")))
    return CALCULATE(SUM(' Fact_Sales'[Gross_Sales]), FILTER('Dim_Day','Dim_Day'[Month_ID]>=VALUE(m) && 'Dim_Day'[Month_ID]<=VALUE(YEAR(TODAY())&FORMAT(TODAY(),"mm"))))

Apart from the above two measures, a 3rd measure (conditional measure) is created to calculate either the daily sales or monthly sales depending on the slicer selection:

 

Sales = SWITCH(MIN('Date_Hierarchy'[Time_Granularity_Order]),1,[Sales_Day],2,[ Sales_Month])

 

Finally, I could then create a bar chart with Sales on the Y-axis and Time-Value on the X-axis of the chart (the same can be implemented on any other visual like grids, pie charts, line charts).

 

After implementing the above approach, on switching between the slicer values, the graph dimensions will change.