Forum Discussion
Calculated column to lookup multiple matching values across multiple tables
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.
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
4 Replies
v-lili6-msftCommunity Support
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
Thank you for this v-lili6-msft. Worked perfectly
BIABResolver I
Ensure your table relationships are set properly.
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.
