Lookup Multiple Values that are mapped through three different 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-shex-msft · Accepted solution]

Hi @antbob ,

You can use content table to create a table visual, then add below measure formulas to lookup related fields values:

Merge ProductID =
VAR _conlist =
    CALCULATETABLE ( VALUES ( Content[ContentID] ), VALUES ( Content[Content] ) )
RETURN
    CALCULATE (
        CONCATENATEX ( VALUES ( Mapping[DestinationID] ), [DestinationID], "," ),
        FILTER ( ALLSELECTED ( Mapping ), [SourceID] IN _conlist )
    )

Merge Products =
VAR _conlist =
    CALCULATETABLE ( VALUES ( Content[ContentID] ), VALUES ( Content[Content] ) )
VAR _destID =
    CALCULATETABLE (
        VALUES ( Mapping[DestinationID] ),
        FILTER ( ALLSELECTED ( Mapping ), [SourceID] IN _conlist )
    )
RETURN
    CALCULATE (
        CONCATENATEX ( VALUES ( Product[ProductID] ), [ProductID], "," ),
        FILTER ( ALLSELECTED ( Product ), [ProductID] IN _destID )
    )

Regards,
Xiaoxin Sheng