Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Is There any way to fix Column Widths in a Matrix

In a Matrix visualization, is there any way to fix the column widths so they don't change. Specifically, I have a matrix that has two Values per Column Item: 

 

Project 
CostUnit Rate 

 

I always want the Cost column to be one uniform size and the Unit Rate to be a different uniform size for each Project which then fixes the uniform width of the Project field. Power BI Desktop seems to retain the width values for each Project-Cost and Project-UnitRate Column independantly as I can have different widths for Project A vs Project B. That is correct, but as I ad new projects via data updats, then I (or the end user) needs to adjust each one as they come along. Shouldn't I be able to preset these values, presumably under the column formatting section?

 

Is there an easy way to do this (if so please let me know) or is this an enhancement that needs to be added at some point?

1 ACCEPTED SOLUTION

Hi @Anonymous,

 

According to my test,

1. When the "auto-size" of "column headers" is off, the column will keep its length, which won't change automatically.

2. When the new data is added the first time, the column would fit its width automatically. 

3. First set "word wrap" on, then set "auto-size" off, it would satisfy your scenario.

 

Please have a try. 

 Is There any way to fix Column Widths in a Matrix2.jpg

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

23 REPLIES 23
David_QH
Advocate I
Advocate I

New update due to new functionality added from PowerBI Desktop Preview item - PowerBI Project Save Option :

 

How to set table or matrix column widths in a Power BI report — DATA GOBLINS (data-goblins.com)

It's an interesting article, thank you for sharing.  I mean, editing a JSON in VSCode is not for everyone, but as you said in the article, it's slowly "becoming easier".  🙂

 

Have you tested the usecase in which the column header values change?  I have problems with fixed columns width for a matrix displaying 4 columns of rolling years (FY2020, FY2021, FY2022, FY2023).  When FY2024 came in and FY2020 was kicked out, because the underlying data only has 4 years, only the first 3 columns kept their fixed width.  It looks like PBI somehow binds the column's width with the value (FY2020, etc.), so I was wondering if the metadata trick you explained in your article might be more resilient?

 

Thanks

 

--mo

Mikebuci99
New Member

Hello! I've discovered a workaround that may resolve the spacing issue in Power BI. Here's what you can try:

  1. Go to the "Specific Column" dropdown.
  2. Select "Value Decimal Places" to add more decimal places, which should expand the Matrix.
  3. Once you've found the right size, go to the "Column Header" option and turn off Autosize.
  4. Return to the "Specific Column" dropdown and remove the extra decimal places you added in step 2.

Let me know if this helps!

You are BRILLIANT. Crazy that we have to find sneaky workarounds to do such simple tasks, but there is a joy in being so bloody clever, surely?? 😀 Thank you for sharing!!

androo
Advocate II
Advocate II

There are workarounds, like the one mentioned above, but, they are partial solutions at best and don't work in all (or enough) scenarios. MS really needs to provide something for this. It is immensely frustrating and an utter waste of "our" time, to have to fiddle around with a mouse getting columns in one or more matrix "visualisations" to line up. It really is so much easier in Power Pivot inside Excel and unless what you are working on needs to be published widely, I'd just use pivots in Excel derived from the data model where this is just so much quicker and more flexible. In Excel with PP you can set widths for multiple "visualisations" at once ("visualisations" are called Pivot Tables or Charts, there) . You can also set widths numerically and you are not restricted to having all columns the same width. Copies of pivots or returned Power Query tables can also inherit the same formatting even, I think, when you change the measure in the values. Power BI isn't the right tool for everything, though some managers now think it is because of the hype. I'm increasingly becoming aware of how much Power BI is focussed on the visuals, which is fine. But, getting the numbers and displaying them in an acceptable way is, I think, much faster in Excel Power Pivot where you have access to M Code (Power Query) and Dax (Power Pivot) just as you do in Power BI. There is a third-party tool, I think endorsed by MS, Dax Studio I think it's called, which may address some of this, but, for many of us working in corporate environments that means a struggle to get that app past your IT people and life is too short.

With regard to the MS site where you used to be able to add your voice to requests for changes or new features to MS Apps, I think that was moved sometime in 2020/2021. Shame they didn't leave a link to the new location, but, perhaps that's not an accident.

vladnicolau
Regular Visitor

It’s incredible how much I’ve struggled with this thing.

Here's my solution: I’ve just renamed my columns by adding empty spaces in the existing column name, for all my matrix columns. Example:

  • column name before: AB
  • column name after:       AB     

by using spaces, I could control the column width of all my columns (I’ve used word to add the number of spaces needed for every column, depending on the column’s name, so that in the end all columns have the same width.

 

Then I just switched the Auto-size column width to “On”

Now all my columns have the same size, finally.

best regards, Vlad.

Terp
Advocate III
Advocate III

4 years later and many of us are still sizing column widths by eye...across 20+ years of matrix data (well, trying to...won't compile 20 years of monthly data, but I digress).

 

 

Anonymous
Not applicable

Hi,

 

Stumbled across this post when looking for a solution to fix column widths in a matrix visual. Seems this is still not supported, but we can vote for it at the PBI idea site:

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19568263-set-an-exact-column-widt...

 

Michael

Looks like they deleted it!  When I click on your link I get a "page not found" error.  To bad because this is still not possible 😞

Many thanks, Micheal. I voted and will also share with our community to vote for this feature.

Anonymous
Not applicable

Hi,

 

Stumbled across this post when looking for a solution to fix column widths in a matrix visual. Seems this is still not supported, but we can vote for it at the PBI idea site:

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19568263-set-an-exact-column-widt...

 

Michael

odoro
Frequent Visitor

has anyone found a fix for this?  I have created a matrix that returns data for a rolling 12 week period.  The column header is the financial week number and the date of the last date of the financial week. 

 

Once I have published the report and the weeks roll over the column width loses the word wrap and takes on the full length of the text of the week header.

 

It is a fixed 12 column matrix and I need it to remain so but the header of the columns will change as the weeks roll over.   I need it to maintain the word wrap and fix the width.

odoro
Frequent Visitor

Word Wrap is ON and auto-size is OFF

v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

 

Please set "Auto-size column size" to "off". It will work according to my test. Maybe you need to upgrade Power BI Desktop to the latest version (2.48.4792.721 64-bit (July 2017)).Is There any way to fix Column Widths in a Matrix.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

This goes beyond the Auto-Size option.

 

Once Auto-Size is turned off, I am trying to understand how Power BI then determines the column size for new columns that are added to the matrix. So I might have 3 projects to start out with six total columns. I can set the size for each of those with the Auto-Size turned off. But the next time I refresh, two new projects are added which creates 4 new columns and I'm trying to figure out how these are sized. I don't want to have to force the end user to adjust column widths every time new information is added to the report.  In the data set I am using the Project field has a number and description component so it might look like this:

 

|12345 - Intercontinental Roadways Terminal #6|

|Cost          |   Unit Rate                                        |

 

and I want the columns to be collapsed so the Project always word wraps based on fixed widths for the Cost and Unit Rate columns:

 

| 12345 - Intercontinental | 45678 - Project B789       |

|  Roadways Terminal #6   |                                         |

| Cost             | Unit Rate   | Cost             | Unit Rate   |

 

I am pretty sure the functionality does not exist at this time, but I'm trying to confirm that because it is needed for the report I am designing. I wanted to see if there was any work around that might be available.

 

Hi @Anonymous,

 

According to my test,

1. When the "auto-size" of "column headers" is off, the column will keep its length, which won't change automatically.

2. When the new data is added the first time, the column would fit its width automatically. 

3. First set "word wrap" on, then set "auto-size" off, it would satisfy your scenario.

 

Please have a try. 

 Is There any way to fix Column Widths in a Matrix2.jpg

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This does not work when using Date Hierarchy. The column widths change with the dates.

For example, if you have Year/Month and are showing a running 12 months; the next month will not be the same size as the column that was once there.

The only way I have been able to get around this is to set the column widths for the next 24 months. It retains the set widths when the months roll around.

From my testing, Setting the Auto-Size to off and the word-wrap to on, works but only at the top level column. If you have drilled-down, there does not seem to be a solution to fix the column width at this time.

This did not work for me as well.  Could this be because I'm using the on-premise version of Power BI (August 2018)?

Is there a way also to fix the "Row Headers" in a matrix?
Even if I fix the "Column Headers" the table still "jumps around" when drilling down in the rows.
TIA

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.