Forum Discussion
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?
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.
7 Replies
- MattAllington
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.
TheBIGuy_PBIHelper II
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 resultAny help is appreciated
- MattAllington
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
- MarkyMark1
Advocate I
Hi Matt is there are way to get this to only Concatenate unique values? Thanks.
- MattAllington
Replace "filteredTable" with VALUES(filteredTable[Column])