Have a requirement of changing the context of the visual based on slicer selection? It can be possible in Power BI by following a very simple trick. This blog is along the same concept of one of my previous blogs.
Let’s get started!
- Data Model
- Visual with Dynamic axis
- Make the Title Dynamic
Like the requirement from the previous blog, another common requirement is to switch the entire calculation based on the values selected in a slicer. Again, this can be done using bookmark, but it’ll be cool if we handle this using DAX.
I’ll be using the same Vehicle dataset with one table to demonstrate this example.
We will be creating a single chart while having an option to switch between two types of calculation: ‘Average Speed’ & ‘Count of Vehicles’.
In Query editor, create a new table using enter data & create column having values you would like to use as a slicer. In my case, I’ve creates a ‘Metric’ column having two values i.e. ‘Average Speed’ & ‘Count of Vehicles’.
Visual with Dynamic axis
Now go ahead and create a slicer using ‘Metric’ from the new Metric Table.
Next, create a measure to switch measure switching based on what’s selected in the ‘Metric’ column. For e.g. based on the below formula, the measure will display average when ‘Average Speed’ is selected while it will change to count when ‘Count of Vehicles’ is selected.
,”Average Speed”, AVERAGE(‘Speed Data'[Speed])
,”Count of Vehicles”, DISTINCTCOUNT(‘Speed Data'[Vehicle Sub Type])
Next, create a visual with the above created measure.
Make the title Dynamic
Create a new measure with the same logic used above but instead of calculating the count & average, enter the text to be displayed upon slicer selection. Something like this:
Calc title =
,”Average Speed”, “Average of Speed by Vehicle Type”
,”Count of Vehicles”, “Vehicle count by Vehicle Type”
Next, click on format -> Title -> Click on ‘fx’ (Next to Title text box)
Select ‘Field value’ under ‘Format by’ & the newly created measure under ‘Based on Field’.
The title will now change according to the value selected in the slicer!
Thanks for reading!
Team Lead – Data Analytics
Addend Analytics is a Microsoft Power BI-partner based in Mumbai, India. Apart from being authorized for Power BI implementations, Addend has successfully executed Power BI projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia. Get a free consultation now by emailing us at email@example.com.