Unlock Dynamic Data Display in your Power BI Matrix

In Power BI, the matrix visual is a core visual, but what happens when you need advanced customization? Specifically, if you require your measures as columns in the first-level column hierarchy followed by other dimensions in the second level? Furthermore, what if you also need to sort a specific measure column and arrange the second-level hierarchy dimension members? Despite its simplicity, achieving this isn’t straightforward using just the matrix visual or the format pane.

Unlike the table visual, where sorting can be easily done by clicking the column header, this functionality is currently unavailable in matrix visuals. As a result, we need to implement some workarounds to achieve the desired configuration and sorting capabilities.

Here’s how to set it up:

1. Modify the star schema in a previous blog, “Create a Star Schema with Power Query.” Unpivot the measures into two columns: ‘MeasureType’ and ‘MeasureTypeValue’. This process stacks the partitioned datasets, allowing for more flexible manipulation.
2. Create a new dimension specifically for measure names. This is done using the enter data functionality, any measure can be linked to DAX and recalcutated during the query phase. This setup enhances flexibility when adding or modifying measures.
3. To sort the members of the second-level column hierarchy effectively, add an empty column in the fact table. This column acts as a placeholder that facilitates sorting without altering the underlying data structure.
4. Implement field parameters to select dimension members dynamically. This feature allows you to alter the appearance of the matrix based on selected criteria, offering a tailored visualization to suit specific analytical needs.

Power Query.. Data Prep

Add three custom columns with the below measure logic to the flat data set in a previous blog “Create a Star Schema with Power Query.”

Create a new dimension using the ‘Enter Data’ icon located in the top ribbon and name it ‘DimMeasureType’. This dimension will play a crucial role in the matrix as it allows you to dynamically add or remove measure columns through a slicer.

Next, integrate this new dimension with the fact table by linking them with the appropriate foreign key. In this setup, you’ll notice the addition of a ‘MeasureTypeShowOrder’ column. This new column is designed to help sort the ‘MeasureType’ column more effectively.

Additionally, we’ve introduced a new metric called ‘Incurred Loss Ratio.’ This measure connects to a DAX formula and is calculated during the query process.

Create an identical table by duplicating the DimMeasureType table.

Rename the table to ‘DimMeasureTypeOrder‘ to enable individual sorting of measure columns in the matrix, enhancing visualization flexibility.

In the fact table, select the four measure columns, right-click, and choose “Unpivot Columns.”

After unpivoting, two new columns are created. Rename ‘Attributes’ to ‘MeasureType’ and ‘Value’ to ‘MeasureTypeValue’.

Next, merge the DimMeasureType dimension into the fact table. Expand the newly created column to retain only the ‘MeasureTypeKey’ attribute. Then, remove the ‘MeasureType’ column from the fact table.

Finally, add an empty custom column to the fact table and change its data type to whole number. This step is a crucial workaround that enables sorting within the second-level column hierarchy.

Table Properties and Data Model

DimAccountYear – Sort AccountYearDesc column by the AcountYearDesc_OrderByDesc column.

DimAccountYear – Sort AccountYearAsc column by the AcountYearDesc_OrderByAsc column.

DimMeasureType – sort the MeasureType column by the MeasuerTypeShowOrder column.

DimMeasureTypeOrder – sort the MeasureTypeOrder column by the MeasureTypeOrderShowOrder column.

Join the DimMeasureType table to the Fact table using a one-to-many relationship. Then, link the DimMeasureType table to the DimMeasureTypeOrder table, also with a one-to-many relationship. You’ll need to enforce the relationship direction to be single-directional, otherwise the resulting join is a one-to-one bi-directional.

The data model should now appear as follows.

Measures

We will need to create our base measures.

Now, let’s create the measure that will be displayed in the matrix. This measure will also set the grand total column of the matrix according to the selected measure in the DimMeasureTypeOrder slicer, thus driving the sort order of the matrix, which is also configured at the visual level.

Lastly, create a measure that formats the background of the subtotal column for the selected measure used for sorting.

Field Parameters..

Field parameters are surprisingly underutilized, given their ability to dynamically alter the appearance of visuals through powerful slicer functionalities. It’s surprising that more attention isn’t drawn to their capabilities.

For this example, we will create two field parameter slicers to ensure the matrix works as expected. To create a field parameter, go to the Modeling tab on the ribbon and click the New Parameter icon. From the dropdown list, select ‘fields’. We’ll begin with setting up dimensions to dynamically change the rows of the matrix.

You can customize the generated slicer by adjusting its formatting options. Change the selection property to ‘single selection’ to allow only one option to be chosen at a time.

Next, repeat the same process for the ‘Account Year Sort’. This will generate another slicer, which should also be formatted to allow only a single selection.

Visualisations..

Create three slicers from DimMeasuerType, DimMeasureTypeOrder and DimAccountYear. Format as below.

Add a matrix visual to the canvas.

Add the Selected Dimension field paramter created earlier to the Rows.

Add MeasureType, SelectedYearOrder field paramter and the EmptyCol attribute from the fact table. Ensure they are added in the order as below.

Add ShowMeasure measure to the matrix values.

Click the extend icon down to the lowest level.

The matrix should now appear as follows.

Sort the matrix in descending order by the Show Measure.

In the right format pane, navigate to the ‘Cell Elements’ area within the series section and select the ‘show measure’ option. Click on the conditional formatting icon. Make sure the background options are configured as follows.

The matrix visual is now complete.

There are numerous options for customizing and manipulating the core Matrix visual. I’d love to hear your thoughts on this approach. If you have a simpler or more effective method to achieve similar results, please feel free to share your insights.