Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
Hi @Butterfly ,
try this measure
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Butterfly ,
try this measure
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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] & ")"), ", "))
Hi @Butterfly ,
you must use the function CONCATENATEX
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |