cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
maronis
Helper I
Helper I

Showing multiple text values in a matrix

Hey all, I'm trying to use PowerBI to create a 'nine-box' report - basically, a matrix where you have 'performance' on one axis, 'potential' on the other axis and then you have names of individual employees in each box based on their performance/potential ratings.

 

In PowerBI, I can create a matrix that will display the first name in each box, but I can't figure out if there's a way to use the Matrix visualization to display every value that matches the criteria for that box in the matrix. Any ideas on how I can do this? Thanks!

1 ACCEPTED SOLUTION

Hi @maronis,

Create a measure in your table using DAX below.

Measure = CONCATENATEX (VALUES(Table1[Employee Name]), Table1[Employee Name], ", ")

Then create Matrix visual as shown in  the following screenshot and you will get expected result.
1.PNG

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
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

11 REPLIES 11
tonyrutt
Frequent Visitor

This was a super helpful answer that enabled me to add additonal values into a grid. However is there a way to add additional rows? We have mutliple promotions to the same customer on the same day, I want to break those out onto separate lines for each customer while still retaining the concatenation to have multiple values in the grid.  In essence what I have is this, what I want to add are the lines in red. Thanks in advance for any suggestions. 

 

PBI question.PNG

 

v-yuezhe-msft
Microsoft
Microsoft

Hi @maronis,

Could you please share sample data of your table and post expected result here?

Thanks,
Lydia Zhang

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

Absolutely. So I'm hoping to take data that looks like the below where there is a Performance rating and Potential value for each employee:

Source data.jpg

And the output should be a matrix that looks something like below, where Performance is on one axis, Potential is on another axis, and all values for each box in the matrix are populated.

 

Output.jpg

 

Thanks so much for your help!

Hi @maronis,

Create a measure in your table using DAX below.

Measure = CONCATENATEX (VALUES(Table1[Employee Name]), Table1[Employee Name], ", ")

Then create Matrix visual as shown in  the following screenshot and you will get expected result.
1.PNG

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
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

Thanks for this! Is there a way to do this DAX, but also add an addition text field next to the name? For instance, the result would look like:

"NAME" + "another field with additional info"

 

I was thinking the DAX could look something like: Measure = CONCATENATEX (VALUES(Table1[Employee Name]), Table1[Employee Name] + (Table1[Additional Info]), ", ") -- this doesn't work, but is something similar to this possible?

 

Thanks!

Nicole

Anonymous
Not applicable

Hello, 

 

How can I add a line break in between values instead of having them all on the same line?

 

 

Capture.PNG

 

Anonymous
Not applicable

Solved by adding UNICHAR(10):

 

Measure = CONCATENATEX (VALUES(Merge1[Assignments.ProjectName]),Merge1[Assignments.ProjectName], UNICHAR(10))

Hi,

 

This has helped me a lot and is exactly what I was looking for. However, I am having a peculiar issue with this in my matrix. My matrix cells are not displaying the entire value of the concatenation by default. I can see the values when I hover over the cell in the tooltip but they dont show up by default. This is even more pronounced when I filter the visuals using slicers. Some values just get dropped off from the visual and I have to manually adjust the column width repeatedly to show them. Clearly adjusting column width is not an optimal solution for the users. Any help would be appreciated,

 

Thank you,

Leena

@v-yuezhe-msftI ran into a similar problem and then found this post which solved my problem. I wanted to see how far dyanmic this formula is and it surprised me.

 

I created a sample table to tase it on two CATs and it succedded in both the cases.

 

Capture30000.PNG

I was wondering if you can please shed some light on this to help me understand how does the formula works on parts. It is simply great. I fail to understand how the formula slices up values based on the different CATs





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

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


This is awesome !!!





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

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Awesome, thanks so much!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.