Achieving Dynamic Dimensions in Power BI

| Last Updated August 10, 2021 - by vignesh balaji under data visualization

The capabilities of the Power Platform continue to increase with every monthly release. In many ways, Power BI has already dramatically surpassed the competition. However, there do remain a few gaps in functionality, most of which are already scheduled on the upcoming product roadmap.

For advanced report developers, one of the most significant of these is the ability to dynamically select dimension columns for visualization granularity. This is not currently provided by the existing DAX functions. There will almost certainly be an update to address this in the future.

In the meantime, however, with Power Query data manipulation and other advanced techniques, we are able to deliver dramatic results.

Use Case:

In one of our recent use cases, we were given a requirement to visualize the fact data across multiple granularities in an axis (say ‘x’ axis) of a column chart with the help of a single slicer. Since this cannot be achieved directly in Power BI, we followed a trick to achieve our results.

Our Approach:

In order to achieve these dynamic dimensions in our visuals, we used the flexibility of Power BI’s native Power Query with the following steps.

Initially we tried to create a copy of our fact table with an index column added, which later acted as a key for the relation between the original fact and newly created table.
We kept the necessary dimension columns along with the index column and removed other columns from the new table.
Here comes the magic, we selected all the necessary dimension columns and chose the unpivot columns option available in power query. This option will allow transformation of all the granularities (column names) under a single entity named ‘Attribute’ and the values of those granularities under the entity ‘Value’.

Before unpivot columns

After unpivot columns
Now we defined the relationship using the index column as a key between the original fact table and the newly created unpivoted table.
Using the Attribute column as a filter or slicer across the fact data we filtered the data across multiple dimensions. We were able to place the ‘Value’ column in the axis of the chart so that the dimensions would be dynamic based upon the slicer selection. For example when we choose ‘Category’ from the granularity selection slicer, the chart will be displayed with the ‘Category values’ in an axis.
In addition to this we were able to see the propagation of filter passing from raw data (fact table) to the unpivot (newly created table) with single direction, but in our case the direction of the filter had to be from the unpivot table to raw data table. In order to achieve this, we used ‘RELATEDTABLE’ a DAX function instead of bidirectional filtering between these two tables by considering the performance of the queries to be at an optimum level.
Revenue = CALCULATE (SUM ( ‘Raw data'[Revenue] ), RELATEDTABLE ( Unpivot ) )
In the Demo report below, we were able to attain control over the axis of the charts and slice the data with respect to the selected granularity by filtering the selections under the Granularity Selection slicer.