Show all items in table visual from related data tables where not all items are related.

[Anonymous]

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
...	...	...	...

[amitchandak · Accepted 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])