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!

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

  1. Tony Hartigan on said:

    Hi Nick,
    Great hack. As someone who is OCD about symmetry, this was a gem.
    Tony

  2. Haley Duong on said:

    hi Nick, thanks for this but it doesnt seem to work. when i drag the “set width” measure into my matrix it is shown beside my real measure and not below it like you have demo here. any solution?

  3. Nick Edwards on said:

    Hi Haley,

    Thanks for your message.

    Could you check you have got the “Show on rows” toggled to “On” in the format pane of your matrix?

    Cheers,
    Nick

  4. Frank Blau on said:

    When I remove the “Pad”measure the widths revert to the preset ones…

  5. Matt Page on said:

    Not only did this help me with column width, but I also had no idea about “Show on Rows”. Brilliant – thanks!
    Matt

  6. sophie dackombe on said:

    Great hack , and so simple to follow , don’t think it works for me as i want to wrap the text to make them narrower.

  7. Thanks for the post. It helped to fix the column width which is not yet available in Power Bi. The method is working for fixing the column width of all values columns except for Total columns. I turned off word wrap. Is there any turn around for this or do I need to format differently for totals?

Leave a Reply

Your email address will not be published. Required fields are marked *

*

370,651 Spambots Blocked by Simple Comments

HTML tags are not allowed.

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
Microsoft Gold Partner

Data Platform MVP

Power BI Sentinel
Frog Blog Out
twitter
rssicon