0845 643 64 63

How to make your matrix column widths all equal to each other in Power BI using DAX.

Have you ever come across an issue where your Power BI matrix column widths just aren’t the same width and visually just don’t look right?

Unfortunately (as of April 21’) there is no easy way to make all column widths equal in the format pane of a matrix visual.

However there is a hack to set the width of your all columns in a matrix so that they are all equal and pixel perfect with DAX!

How do you do this I hear you ask?

Firstly create a new measure called ‘Set Column Width’ and enter a string value equal to the length of your longest column title. In my case my longest title is “Front Derailleur Cage” and this has a length of 21 characters including spaces. Therefore I need to set my DAX expression to be a string which is 21 characters long. In my example I’ve just created a string of 21 asterixis wrapped in speech marks – but this can be any combinations of characters you like!

The next job is to go to the format pane of your matrix and set the “Show on rows” toggle equal to ‘On’.

Next make sure the ‘Auto-size column width’ is set equal to ‘On’.

Now drag your newly created DAX expression (in my case ‘Set Column Width’) on to the values field of your matrix.

You’ll then notice that your matrix will look similar to the snip below – a little bit of a mess! But not to worry this all part of the plan!

Next go back the format pane of your matrix and set ‘Auto-size column width’ to ‘Off’.

Now remove your ‘Set Column Width’ measure from the visual by clicking the ‘X’ symbol on the field pane.

Finally increase the width and height of your matrix visual to accommodate the increased column widths.

You now have pixel perfect column widths which are all equal to each other!

A huge thanks to the brilliant MVP Ruth Pozuelo Martinez (@ruthpozuelo) from curbal.com for this hack! It’s a been a massive help for my Power BI reports here at Purple Frog! Hopefully the Power BI team will release a proper solution in the matrix format pane soon!

1 2 3 13
Power BI Sentinel

The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.

Authors:

Alex Whittles
(MVP)
Reiss McSporran
Jeet Kainth
Jon Fletcher
Nick Edwards
Joe Billingham

Data Platform MVP

Power BI Sentinel
Frog Blog Out
twitter
rssicon