topic Fixed column width in matrix visualisation in Developer

Fixed column width in matrix visualisation

moaqvarnlof — Mon, 18 Nov 2024 15:00:26 GMT

Hello!

I have a matrix with the setting "values to rows" activated.

Since the column headers originally are values from a data column, the column headers change when I change the active filter. So does the width of the columns.

Is there any way to set a fixed column width? Nothing I have tried has worked.

Thanks in advance,

Moa

Re: Fixed column width in matrix visualisation

KNP — Mon, 18 Nov 2024 17:20:44 GMT

Hi Moa,

If you select your matrix, go to formatting and search for "auto", you will get this...

Turn that setting off and it won't resize anymore.

Re: Fixed column width in matrix visualisation

moaqvarnlof — Mon, 18 Nov 2024 17:46:41 GMT

Thanks for you answer, but unfortunatly, that does not work. The column header content and its width still change when I filter the data..

Re: Fixed column width in matrix visualisation

KNP — Mon, 18 Nov 2024 18:22:34 GMT

I'm not sure I'm understanding your scenario fully.

This is what I thought you meant...

Re: Fixed column width in matrix visualisation

moaqvarnlof — Mon, 18 Nov 2024 18:28:57 GMT

Yes that's what I meant, but the problem is when something wider than "Music, Movies and Audio Books" takes place in the column. Then the column width increases to fit its content.

Re: Fixed column width in matrix visualisation

KNP — Mon, 18 Nov 2024 18:50:49 GMT

There must be something different about your scenario, because if I resize to the smallest and turn the setting off, I can't get it to do that.

Re: Fixed column width in matrix visualisation

moaqvarnlof — Mon, 18 Nov 2024 19:26:16 GMT

In my scenario, I have "Color Reference" under Columns in the matrix-settings, with "Values to rows" activated. Then, I filter out the top 15 values based in their corresponding sales, from the color column. This results in a matrix with 15 columns (black, red, pink, ..). There are a lot of different colors in "Color Reference", so when I change the date filter to another month for example, other colors might be in the top 15 when sorted by sales, and thereby the column headers change. When this happens, the column widths change even though I turn off the auto-size width.

Re: Fixed column width in matrix visualisation

Anonymous — Tue, 19 Nov 2024 05:56:35 GMT

Thanks for the reply from KNP , please allow me to provide another insight:

Hi, @moaqvarnlof

In my practical tests, prior to disabling the automatic width adjustment:

The row width is influenced by the size of your values and the text size of the column headers.

Once you disable this feature, the current column widths will be retained (please note that the widths of each column may not be consistent).

No matter how you adjust the font size or add filters, the widths will remain unaffected.

As for why your widths may have changed, I suspect it could be a version issue. I recommend upgrading your version to see if this resolves the problem.

Here is the download link:

Download Microsoft Power BI Desktop from Official Microsoft Download Center

If you wish for each column to maintain the same width, you might consider inserting a row (or column) in the visualisation as the longest column width.

Once you disable the automatic width adjustment, the sizes will remain fixed.

1.This is the original width:

2.After inserting the standard column and disabling the automatic width adjustment:

3.Replacing the value will not change the width either.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

Please find the attached pbix relevant to the case.

Of course, if you have any new discoveries or questions, please feel free to get in touch with us.

Best Regards,

Leroy Lu

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

Re: Fixed column width in matrix visualisation

moaqvarnlof — Tue, 19 Nov 2024 08:19:17 GMT

Thanks a lot for your response and ideas!!

I have the lastest Power BI Desktop version, so I don't think that's the issue..

The problem shows using your file aswell. Here, the column widths change if I instead choose the 3 colors with the lowest sales:

So if the content of the column header change, the width adapts to its new content.

Re: Fixed column width in matrix visualisation

DataNinja777 — Tue, 19 Nov 2024 22:50:41 GMT

Hi @moaqvarnlof ,

To ensure consistent column widths dynamically in Power BI without manually adjusting them, you can use a DAX measure that pads the values or headers using the CONCATENATEX function. This method standardizes the length of the text displayed, which in turn helps Power BI render uniform column widths.

Start by creating a DAX measure that appends spaces to the values in your data. For example, you can write a measure like this:

PaddedValues = CONCATENATEX( VALUES('Table'[DataColumn]), 'Table'[DataColumn] & REPT(" ", 20 - LEN('Table'[DataColumn])) )

This measure ensures that every value is padded to a fixed total length. Replace 20 with the desired length to suit your formatting requirements.

Next, if the column headers are also dynamic and causing the matrix to resize, create a similar calculated column for the headers:

PaddedHeaders = CONCATENATEX( VALUES('Table'[ColumnHeader]), 'Table'[ColumnHeader] & REPT(" ", 10 - LEN('Table'[ColumnHeader])) )

This calculated column works in the same way by appending spaces to ensure a consistent header text length.

If you are using the padded values for row/column headers in a matrix: Use a calculated column, as measures can't be dropped into the headers section.
If you are using the padded values dynamically in the Values area or want them to adjust with slicers: Use a measure for flexibility.

Power BI will render the matrix with consistent column widths because the padded text ensures that the visual space required remains constant. This approach eliminates the need for manual adjustments and provides a dynamic solution for managing uniform column widths effectively.

Best regards,

Re: Fixed column width in matrix visualisation

KNP — Tue, 19 Nov 2024 18:49:21 GMT

I think I understand now.

@DataNinja777's idea caused me to test some more.

- If you create a simple measure

column_width = REPT("X", 40)

- Turn off any filtering to ensure all columns are showing.

- Turn ON 'Auto-size width'

- Drop measure into values.

- Turn OFF 'Auto-size width'

- Remove measure.

- Filter to test.

Re: Fixed column width in matrix visualisation

moaqvarnlof — Thu, 21 Nov 2024 12:25:24 GMT

Thanks a lot for your answers!! Both @DataNinja777 and @KNP! Those are clever solutions to get the width to always stay the same! The problem I still have is that the column width that I need in my matrix is smaller than the length of the column header content. When I manually adjust to the needed column width, the content is divided into 2-3 lines of text inside the column header. This solution would work though if I did not need to narrow each column width down from its original width.

Re: Fixed column width in matrix visualisation

KNP — Thu, 21 Nov 2024 17:52:22 GMT

Hey @moaqvarnlof,

I have a solution that I think may be workable.

In power query (you could probably do this in DAX also as a calculated column), for the column you are using, you could replace spaces with a line feed #(lf) character.

= Table.ReplaceValue(fr1," ","#(lf)",Replacer.ReplaceText,{"CategoryName"})

The result is...

You could probably figure out a way to do every second or third word. I don't have time to do that at the moment.

I hope you can find a workable solution from this.