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!
Blog Structure:
- Scenario
- Data Model
- Visual with Dynamic axis
- Make the Title Dynamic
Scenario
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.
Data Model
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.
Calc =
IF(HASONEVALUE(‘Metric Table'[Metric]),
SWITCH(VALUES(‘Metric Table'[Metric])
,”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 =
IF(HASONEVALUE(‘Metric Table'[Metric]),
SWITCH(VALUES(‘Metric Table'[Metric])
,”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!
Karan Nair
Team Lead – Data Analytics
Addend Analytics