Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |