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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

How to fix column widths in a matrix where column headers change with each refresh

Hello,

Can someone please help me with a solution? I have a Power BI matrix with several column fields (headers) which change whenever the data is refreshed. The auto-size option is already turned off and text wrap is turned on. 

Or if there's some way we can have a bubble matrix in Power BI where we can have some spacing between the column bubbles?

Thanks in advance

@rowsandcolumns 

10 REPLIES 10
cairmor
Helper I
Helper I

The solution to maintain constant column widths in Power BI Table and Matrix visuals is to use the UniChar() function, Len() function and the Repeat function Rept().  You can thus build very specific widths that wont change.
Here is a complex example, checking different columns.

var Char = UNICHAR(8194)
var LENGTH = len('Table'[ReportingPeriod])
var PPSpaces = 15
var CYSpaces = 5

Return
-- Check if Reporting Period is 4 characters
    IF (
    Len('Table'[ReportingPeriod]) <> 4,
-- Create a 15 space string for Plan Period
    Rept(Char,1) & 'Table'[ReportingPeriod]
 -- Create a 15 space string for Calendar Year
    ,Rept (Char,5) & 'Table'[ReportingPeriod] & Rept(Char,CYSpaces)
     )

@cairmor  Thanks for Posting it ! I made it work with similar solution found in https://www.esbrina-ba.com/the-ultimate-hack-to-set-column-widths-in-a-matrix/ using unicode. The other issue is the Wrapped Matrix columns are getting reset inspite of Text Wrap = "On" and Auto - Width = "off".

cairmor
Helper I
Helper I

the matrix is broken..there is no workaround for this

Preeti_Yadav
Resolver I
Resolver I

Hi @Anonymous ,

 

 For the fix of  the width of the column headers, In properties pannel> column headers>you have to off the Text wrap option, and off the turn off auto size width option., will fix the issue.

Preeti_Yadav_0-1646205607636.png

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! 

Regards,

Preeti Yadav

 

Anonymous
Not applicable

@Preeti_Yadav 

I tried turning off both the auto-size and text wrap, however, whenever the column headers change, i.e, replaced by a new column header name/text, it doesn't work. This is because the headers are also dynamic in nature. 

Is there any way we can completely fix the column width so that it never changes irrespective of any change in the data?

Hi @Anonymous ,

 

Power BI will automaticlly set auto-size width for new column. So currently, the best workaround should be turn off Auto-size with and Text wrap option in format mentioned above and then drag the width of column header manually.

Your demand is a good idea, while it is not supported to implement in Power BI currently.
You can vote up this idea for this function: Auto Fitting Column Width

Or you can submit a new idea to improve the Power BI.
It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

 

Best Regards,
Rico Zhou

 

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

@v-rzhou-msft @Anonymous  Did you get a Solution. with May 2023 PowerBi desktop i still am observing this issue. When the Periods are used as column for a selected period the columns will change. i lose all the formatting. the Auto-width disable and Text wrap does not work. Please advise.

++

The matrix formatting in a bubble format is not possible for now as theres no such option is available for cell formatting.

 

Regards,

Preeti Yadav

Preeti_Yadav
Resolver I
Resolver I

Hi @Kavish_Ahuja
It will be helpful,if you will provide some screenshots of the visual and ellaborate it .

 

regards,

Preeti Yadav 

Anonymous
Not applicable

Hello @Preeti_Yadav ,

As mentioned, I have a matrix with these headers 

Kavish_Ahuja_0-1646203131318.png


And whenever I refresh the data, the number of headers and the values change, due to which the width of the headers also change (screenshot below) 

Kavish_Ahuja_1-1646203196027.png


Would it be possible to fix the width of the column headers where it doesn't change irrespective of the change in header name or number of headers?

Moreover, would it be possible to set the matrix formatting in a bubble format instead of rectangular cell blocks (screenshot below)

 

Kavish_Ahuja_0-1646203888131.png

 

 
 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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