Calculated column to lookup multiple matching values across multiple tables

[antbob]

Hi all,

I am having some trouble building this out, hopefully as a caluclated column, but can approach this differently if need be.

I have three different tables (Content, Mapping, Product).

Each piece of content and product is a unique listing in their respective tables, but a piece of content can be associated to multiple products, hence where the mapping table comes in.

I am trying to create calculated columns within the content table that show where each piece of content is published (ie. ProductA, ProductB, ProductC). This data is pulled from a database and contains a few thousand pieces of content I need to map out.

Content Table

Content	ContentID
ContentA	1234
Contentb	1235
Content-C	1236
Content D	1237

Mapping Table

SourceID	DestinationID
1234	5242
1234	5244
1235	5243
1236	5243
1236	5242
1236	5245
1237	5243
1237	5244

Product Table

Product	ProductID
ProductA	5242
ProductB	5243
ProductC	5244
ProductD	5245

End Goal Content Table

Content	ContentID	ProductID	Products
ContentA	1234	5242, 5244	ProductA, ProductC
Contentb	1235	5243	ProductB
Content-C	1236	5242, 5243, 5245	ProductA, ProductB, ProductD
Content D	1237	5243, 5244	ProductB, ProductC

I would greatly appreciate anyone's assistance on this one.

[v-lili6-msft · Accepted solution]

hi, @antbob 

You could use CONCATENATEX Function to get it.

Step1:

Create the relatuoship as below:

Note: Cross filter direction must be "Both"

Step2:

Add two calculate column

ProductID = CONCATENATEX(RELATEDTABLE('Product'),[ProductID],",")

Products = CONCATENATEX(RELATEDTABLE('Product'),'Product'[Product],",")

result:

Best Regards,

Lin

[antbob]

Thank you for this @v-lili6-msft. Worked perfectly

[BIAB]

Ensure your table relationships are set properly.

[antbob]

Thanks for the input, but unforuntately I am accounting for multiple products that are associated with a piece of content.  Please take a look at the last table I provided which shows the expected result I am working towards.