Power BI has a lot of features which helps developers to satisfy the requirements of the end-user in a very short time. Sometimes though, different work arounds are needed to get the desired output. I’ll be showing one such work arounds
Let’s get started!
Blog Structure:
- Scenario
- Data Model
- Visual with Dynamic axis
Scenario
One common requirement is to have the ability to change the perspective of the chart based on values selected in a slicer. If we want to switch between different axis on a visual, we can easily do it using bookmark & selection pane feature. But what if we want to do it based on a slicer & not a button.
Data Model
I’ll be using a simple Vehicle dataset with one table to demonstrate this example.
We will be creating a single chart while having an option to switch between ‘Vehicle Type’ & ‘Vehicle Sub Type’ as it’s axis.
In Query editor, take reference of the main table & only keep the columns to be used as axis. Next step is to remove duplicates based on both it’s combination.
Then, unpivot both the columns to get column names in ‘Attribute’ & the actual categories as ‘Values’. Remove duplicates from the Values if required.
Visual with Dynamic axis
Now go ahead and create a slicer using ‘Attribute’ from the new Axis Table. Create another visual with ‘Value’ as it’s axis.
Next, create a measure to join the newly created table with our old flat table.
Dynamic axis calc =
IF(HASONEVALUE(‘Axis Table'[Attribute]),
SWITCH(VALUES(‘Axis Table'[Attribute])
,”Vehicle Type”, CALCULATE(AVERAGE(‘Speed Data'[Speed])
,TREATAS(VALUES(‘Axis Table'[Value])
,’Speed Data'[Vehicle Type]))
,”Vehicle Sub Type”, CALCULATE(AVERAGE(‘Speed Data'[Speed])
,TREATAS(VALUES(‘Axis Table'[Value])
,’Speed Data'[Vehicle Sub Type]))
)
)
As you can see, we have used TREATAS function to match the values from old table to the new axis column from ‘Axis Table’. This will enable the measure to calculate the values according to the selection made in the new table without any real relationship in the data model.
We can now use the above measure in our visual. The axis dynamically switches based on the selection in the slicer!
Thanks for reading!
Karan Nair
Team Lead – Data Analytics
Addend Analytics