Forum Discussion

alinamarinbadea's avatar
alinamarinbadea
Frequent Visitor
1 year ago

Cross-check two tables

Hello! I have a bit of a complicated task.  We have a list of spare parts, each with one or more variants. Each Variant is compatible with a certan Product. Compatibility is either tested (Yes) or i...
Need Help
Kedar_Pande's avatar
Kedar_Pande
Icon for Super User rankSuper User
1 year ago

Table for Compatible Products:

CompatibleProducts = 
ADDCOLUMNS(
SUMMARIZE('Table1 Parts with variants', 'Table1 Parts with variants'[Part]),
"Compatible Products",
CONCATENATEX(
FILTER(
SUMMARIZE(
'Compatibility',
'Compatibility'[Part], 'Compatibility'[Attribute],
"IsCompatible", MAXX(FILTER('Compatibility', 'Compatibility'[Value] = "Yes"), 'Compatibility'[Attribute])
),
NOT(ISBLANK([IsCompatible]))
),
[IsCompatible],
"; "
)
)

Table for TBD Products:

TBDProducts = 
ADDCOLUMNS(
SUMMARIZE('Table1 Parts with variants', 'Table1 Parts with variants'[Part]),
"TBD Products",
CONCATENATEX(
FILTER(
SUMMARIZE(
'Compatibility',
'Compatibility'[Part], 'Compatibility'[Attribute],
"IsTBD", MAXX(FILTER('Compatibility', 'Compatibility'[Value] = "TBD"), 'Compatibility'[Attribute])
),
NOT(ISBLANK([IsTBD]))
),
[IsTBD],
"; "
)
)

Create a Final Merged Table:

FinalResult = 
ADDCOLUMNS(
SUMMARIZE('Table1 Parts with variants', 'Table1 Parts with variants'[Part]),
"Compatible Products", 
LOOKUPVALUE('CompatibleProducts'[Compatible Products], 'CompatibleProducts'[Part], 'Table1 Parts with variants'[Part]),
"TBD Products", 
LOOKUPVALUE('TBDProducts'[TBD Products], 'TBDProducts'[Part], 'Table1 Parts with variants'[Part])
)