Make to make a column with list of related values?

[Anonymous]

anonymised data

I have table of "Elements", that is related to another entity of "Categories" with a many to many relationship: 

from that I have a table visual for "Elements": 

I want to add another column, that lists all the categories this element belongs to. 

If I add the column with the category name, it looks, as expected, like this: 

But I aim for it to look like this: 

What is the most suitable way to achieve this?

[mangaus1111 · Accepted solution]

Hi @Anonymous ,

try this measure

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

[Anonymous]

This also works really well, thanks a lot!

Looks kinda like this in my case: 

Categories = CALCULATE(CONCATENATEX(CategoriesElements, RELATED(Categories[CategoryName]) & IF(CategoriesTraining[Comment]="", "", " (" & CategoriesTraining[Comment] & ")"), ", "))

[mangaus1111]

Hi @Anonymous ,

you must use the function CONCATENATEX

CONCATENATEX - DAX Guide

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

[Anonymous]

Thank you for the hint, however, I still couldn't reach my final goal. 

I created a new column: 

Categories = CALCULATE(CONCATENATEX(CategoriesElements, CategoriesElements[CategoryID]), ", ")

Which results in a table like this: 

But instead of these numbers (CategoryID) i want to display "CategoryName" from the related Table "Categories". 

How can I tweak this code so that it displays the CategoryName instead of its ID?

[Anonymous]

After further experimenting I found the complete solution: 

Step 1

Create the Column that lists all related Categories like this: 

Categories = CALCULATE(CONCATENATEX(CategoriesElements, CategoriesElements[CategoryID] ", "))

Result:

Step 2

Display CategoryName instead of its ID: 

Categories = CALCULATE(CONCATENATEX(NATURALINNERJOIN(Categories, CategoriesElements), Categories[CategoryName], ", "))

Result:

Step 3

Add comment of relationship Table: 

Categories = CALCULATE(CONCATENATEX(NATURALINNERJOIN(Categories, CategoriesElements), Categories[CategoryName] & IF(CategoriesTraining[Comment]="", "", " (" & CategoriesTraining[Comment] & ")"), ", "))

Result: 

special thanks to @mangaus1111 for the initial hint to CONCATENATEX