Text in a Matrix

[Cynot]

Hello all,

I am trying to create a dashboard that displays some information in a matrix, but the problem I am having is that the value field I want to display is actually a string.  It is a code that is assoicated with various columns in the matrix, but it appears that powerBI only wants to let me place numerical values in a matrix.

If I try to enter a text field, it just tries to summarize by using "count of" fieldname.  Is there a way I can use text values in a matrix? Or perhaps another visual that will let me accomplish the equvilant?

[MattAllington · Accepted solution]

Yes you can do this, but only when the string returns a single value. Write a measure something like this

=if(hasonevalue(table[column]),values(table[column]))

there is a new function called concatenatex that allows you to build a string of values on the fly in the instance that there is more than a single value returned. Just google the syntax. 

=if(hasonevalue(table[column]),values(table[column]),concatenatex(table,table[column],", "))

[mrinalarora]

This is great help Matt. i have one more question- I am not able to find conditional formatting on the measure created using your formula. Is this feature not available yet? if there is workaround, please let me know.

[MattAllington]

Conditional formatting is available, but it is hard to find. It is located in the main layout section of the visual, not under formatting options. I think you click on the measure in the values section from memory. 

[mrinalarora]

Hi Matt,

As you can see in the picture above, I am only able to find two options- 'Remove field' and 'Quick Calc' and not the conditional formatting option. For normal columns which were directly pulled from excel, I was able to see conditional formatting option.

[MattAllington]

I guess you don't have the latest version of Power BI Desktop.  I just checked and it is definitely there

[KHorseman]

@MattAllington I think conditional formatting only works on measures that return numerical values. I have a matrix with 2 measures, one that returns a whole number and another that returns text. Conditional formatting is only available on the number.

[AntJB]

How can we add new line charatcers in a visualisation, specifically a matrix or table.  It seems you can't do it.  This concatenex is almost there n terms of concatenating text values into a measure, but I want to put each component piece of text on a new line, not just separate with a delimter.  It looks like even if you have a new line character in your source data and just display in a table the new line characters are removed and it all goes on a single line.

[RichaGCS]

@AntJBwere you able to solve this? I have same problem. Dont want the text to be in same line. 

[KHorseman]

@RichaGCSI believe the new matrix preview visual now has word wrap on values. Get the most recent desktop release and activate the feature in the preview options.

[AntJB]

How do we add a new line character as a delimter so each component text value is on a new line.  It seems like even if the data source itself has new lines / carriage returns the matrix & table visualisations (in fact any visualisation) ignores new lines and puts everything on a single line

Thanks

Anthony

[AntJB]

How do we add a new line character as a delimter so each component text value is on a new line.  It seems like even if the data source itself has new lines / carriage returns the matrix & table visualisations (in fact any visualisation) ignores new lines and puts everything on a single line

Thanks

Anthony

[Cynot]

Sorry for the delayed response, I got pulled away on other projects and just got to test this out today.  Thank you!

That worked like a charm.  My only other question would be (and I think the answer is no but I'm being picky), is there any way I can add columns after the pivoted column in a matrix?

In other words if my "columns" value that is being pivoted is product code, and I want the matrix to look comething like:

Order | Customer | Product Codes ... | ... | ...| Due Date | Etc |

Is that possible?

Thanks again!

[MattAllington]

This can be done easily in Excel with Fields, Items and Sets, but I don't believe it can be done today in Power BI.  There are some table updates coming shortly (improved visuals) so let's see if there is any change then.