Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Its expected output is:
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:
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:
Below are the table attributes:
After importing this table into Power BI, I created a slicer based on the Time_Granularity column (with Forced Selection turned On).
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.