Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |