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!
- Data Model
- Visual with Dynamic axis
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.
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 =
,”Vehicle Type”, CALCULATE(AVERAGE(‘Speed Data'[Speed])
,’Speed Data'[Vehicle Type]))
,”Vehicle Sub Type”, CALCULATE(AVERAGE(‘Speed Data'[Speed])
,’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!
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 firstname.lastname@example.org.