Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone,
To simplify the problem, suppose I have one table with distinct items that is connect with several tables with duplicate items:
Distinct Item | Item Family |
A | Family 1 |
B | Family 2 |
C | Family 3 |
Item | Supplier |
Item A | S1 |
Item A | S2 |
Item B | S1 |
Item | Warehouse |
Item C | W1 |
Item B | W2 |
Item B | W3 |
When I create any kind of relationship between this tables (one to one, one to many, many to many) and display them on a table visual, only the items in common with the 3 tables will be displayed.
Item | Family | Supplier | Warehouse |
Item B | Family 2 | S1 | W2 |
Item B | Family 2 | S1 | W3 |
How do I make all the items from the distinct item table appear, showing blank cells where there is information missing?
In order to not duplicate the rows of the table visual, how can I make a measure that outputs in a single string a list of items?
Like so
Item | Family | Supplier | Warehouse |
Item B | Family 2 | S1 | W2, W3 |
... | ... | ... | ... |
Solved! Go to Solution.
Assuming table A also have name like Item A, Item B etc else create a new column
new item = "item " & [item]
Create new columns in table 1
supplier = concatenatex(filter(table2,table1[item]=table2[item]),table2[Supplier])
Warehouse = concatenatex(filter(table3,table1[item]=table2[item]),table3[Warehouse])
Assuming table A also have name like Item A, Item B etc else create a new column
new item = "item " & [item]
Create new columns in table 1
supplier = concatenatex(filter(table2,table1[item]=table2[item]),table2[Supplier])
Warehouse = concatenatex(filter(table3,table1[item]=table2[item]),table3[Warehouse])