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

how to combine text in multiple rows- DAX

I have a table which contains employeess' self-evaluation (column "Achievement"). Is there a way to filter the data by employee and combine them into one measure?

 

text combine in DAX.PNG

 

1 ACCEPTED SOLUTION
MattAllington
Community Champion
Community Champion

If you just want to combine all rows into a single text measure, you can use concatonatex with a pattern like this. 

CONCATENATEX(filteredTable,filteredTable[Acheivement],", ")

 

the filtered table could be done in different ways, including just passing the table name and adding the employees name into the rows section of a matrix. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

7 REPLIES 7
MattAllington
Community Champion
Community Champion

If you just want to combine all rows into a single text measure, you can use concatonatex with a pattern like this. 

CONCATENATEX(filteredTable,filteredTable[Acheivement],", ")

 

the filtered table could be done in different ways, including just passing the table name and adding the employees name into the rows section of a matrix. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hi Matt is there are way to get this to only Concatenate unique values? Thanks.

Replace "filteredTable" with VALUES(filteredTable[Column])



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

I got an error with the measure "a table of multiple values was supplied when a single value was expected". I did manage to solve this using a similar solution you provided below with the dimension table linked to the fact table. Thanks.

HI Matt. This was really helpful and your solution worked for me as well, but I have a different challenge.
Is there a way to concatenate each record only in which the QuestionText is the same?

In other words, concatenate the records when the QUESTIONTEXT is the same?
Ideally I would have two concatenated records, one for each record as a result

 

TheBIGuy_PBI_1-1681243613859.png

Any help is appreciated

 

 

You need a dimension table for your QuestionText (create that in Power Query

Join the dimension table to your fact table

Use the dimension table in the visual (shown above).  Now there is only 1 record per question text instead of 1 record per answer

rewrite your concatenatex to refer to the dimenstion table as the first parameter



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

Thank you! That works perfectly. 

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.