After covering how to dynamically switch measures in Power BI in my previous blog, here I will explore how to switch the dimension on your axis. While exploring this functionality during Dashboard Week, I found many approaches in other blogs but I’ve summarised the two I attempted. Note that neither use the parameter function in Power BI, but rather, require calculations and manipulation of the data structure. For the moment, achieving this interactivity does not seem as straightforward as parameter actions in Tableau, but is still achievable with some extra steps.
1. Unpivot the data into the appropriate structure
To get the data in the correct format, we must first ‘unpivot’ the data. This forces the data into two columns: an attribute and value column. In this example, I want to choose between displaying sales by region, category and segment.
2. Configure the charts using the new data columns
The attribute column can then be used in the slicer for the user to switch between the dimensions. Essentially, we are filtering the data to only display the rows associated with the desired dimension. The value column should be placed on the axis of your desired chart. The title can also be set to dynamically change according to what has been selected. (See my previous blog for a more detailed explanation of how this calculation works.)
Title = IF(HASONEVALUE(‘Orders (pivot)'[Attribute]), “Sales by ” & VALUES(‘Orders (pivot)'[Attribute]), “All Sales”)
The next approach is slightly more complex. However, it means that you do not need to restructure the original data table, and can keep each dimension as a separate column. However, it is then necessary to create some extra tables. (You can also check out this blog where I originally learnt this approach.)
1. Create a dimensions table
The first step is to create a table listing the dimensions to filter the view by. Use these values in a slicer as the equivalent of the ‘parameter control’.
2. Create a joining table
Power BI cannot directly apply this dimension filter to our sales data because the tables are currently unrelated. We need to establish a relation between the dimension and order tables but there are no fields to connect directly. So we need to create a join table that contains a combination of each unique identifier of the orders table (row ID) and dimensions table (dimension). I manually created a table containing each row ID and each dimension (9994 IDs x 3 dimensions = 29982 rows).
We can then use this table to join the orders and dimensions in our model. Verify the relationships are created between the correct fields and that the cross filter direction is set to ‘both’ in both relationships.
3. Create a calculation to switch dimensions
To use this calculation, we must first understand two main functions:
Returns the first value in the column that isn’t blank (the expression could even just be a ‘1’ in this case since we don’t need to evaluate any expressions)
Returns values from another table (a relation must already be established between the two tables)
Similar to the calculation in switching measures, we are telling Power BI to look at the selected value from the dimensions table and return a related column in the orders table accordingly. Then, you can drag this column into the axis of your chart, along with the desired value – I went with sales.
Both approaches above lead to the same result, but feature different ways of structuring the data according to your purposes and preference. Although it took me a while to get my head around these functions, I learnt a lot more about how to improve interactivity in Power BI, and how to use DAX calculations in general.
Here is the final result:
Image source: www.freepik.com