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.


Approach 1

 

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.

Select the dimension columns to unpivot

Resulting data structure

 

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”)

Use the attribute column as the slicer field

Use the value column as the axis of your chart

 


Approach 2

 

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’.

Create table containing dimensions to slice by

 

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).

Sample of the joining table

 

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.

Data model. Note both relations are now many-to-one and bi-directional

 

3. Create a calculation to switch dimensions

To use this calculation, we must first understand two main functions:

  • FIRSTNONBLANK(<column>,<expression>)
    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)
  • RELATED(<column>)
    Returns values from another table (a relation must already be established between the two tables)

SelectedDimension =

SWITCH(

FIRSTNONBLANK(DimensionsTable[Dimensions],VALUE(DimensionsTable[Dimensions])),

“Category”, RELATED(Orders[Category]),

“Region”, RELATED(Orders[Region]),

“Segment”, RELATED(Orders[Segment])

)

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.

Configure your chart to display the calculated dimensions column on the axis

 


Result

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:

 

→ How to switch measures and titles in Power BI

Image source: www.freepik.com