Cross-check two tables

[alinamarinbadea]

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 is under investigation (TBD)

Table1 Parts with variants

Part	Variant
1	1.1
1	1.2
1	1.3
2	2.1
3	3.1
3	3.2
4	4.1
5	5.1

Table 2: Compatibility

Variant	A	B	C	D	E
1.1	Yes	TBD		TBD	Yes
1.2		Yes	Yes
1.3
2.1		Yes		Yes	TBD
3.1			TBD
3.2	Yes				Yes
4.1			Yes
5.1		Yes		TBD

The task is to list compatible products for each part, regardless of the variant (e.g. If Part 1 has at least one variant compatible with product A, product A is listed in the compatible products column). A product shouldn't be in both Compatible and TBD column.

Expected results:

Part	Compatible Products	TBD
1	A; B; C; E	D
2	B; D	E
3	A; E	C
4	C
5	B	D

What I have done so far:

1. In Table Compatibility, Unpivoted Columns 3-5 and filtered out the blanks

2. Added 2 new columns 

Compatible = IF('Compatibility'[Value]="YES",'Compatibility'[Attribute],BLANK())

TBD = IF('Compatibility'[Value]="TBD",'Compatibility'[Attribute],BLANK())

3. Created two new tables:

Compatible = ADDCOLUMNS(

    SUMMARIZE('Compatibility', Compatibility[Variant]),

    "Compatible Products",

    CALCULATE(

        CONCATENATEX(

            FILTER(

                'Compatibility',

                NOT(ISBLANK('Compatibility'[Compatible]))

            ),

            [Compatible],

            "; "

        )

    )

)

and 

TBD = ADDCOLUMNS(

    SUMMARIZE('Compatibility', Compatibility[Variant]),

    "TBD",

    CALCULATE(

        CONCATENATEX(

            FILTER(

                'Compatibility',

                NOT(ISBLANK('Compatibility'[TBD]))

            ),

            [TBD],

            "; "

        )

    )

)

4. Created relationships one to one between Tables Compatible and Parts with Variants + TBD and Parts with Variants

5. Created visual where I have added: Part, Compatible Products, TBD.

The result below is not the expected one:

Part	Compatible Products	TBD
1
1	A; E	B; D
1	B; C
2	B; D	E
3	A; E	C
3	E	C
4	C
5	B	D

Can someone please help me out? Thank you!

[v-linhuizh-msft]

Thanks for the reply from Kedar_Pande.

Hi @alinamarinbadea ,

Based on the description of your problem, I reproduced your question and made some changes, hopefully this will fulfill your needs.Here are the exact steps:

1.Create relationships:

2.Create two new columns in the Compatibility table:

Part = RELATED('Parts with variants'[Part])

UpdatedTBD = 
IF (
    CONTAINSSTRING (
        CONCATENATEX (
            FILTER ( 'Compatibility', 'Compatibility'[Part] = EARLIER ( 'Compatibility'[Part] ) ),
            'Compatibility'[Compatible],
            ","
        ),
        'Compatibility'[TBD]
    ),
    BLANK(),
    'Compatibility'[TBD]
)

3.Replace 'Compatibility'[variant] with 'Compatibility'[Part] and replace 'Compatibility'[TBD] with 

'Compatibility'[UpdatedTBD]:

4.The final result is as follows:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[alinamarinbadea]

Thank you, @v-linhuizh-msft , it worked. However, I have two additional questions:

1. Is there any way to sort this column to say A; B; C; E on the first row instead than A; E; B; C ?

2. How would I adapt the formula if the relationship between tables is many to many (one variant can go in multiple parts)

[alinamarinbadea]

Can anyone assist here please?

[alinamarinbadea]

I don't have the column Part in the Compatibility Table. 

Thied to adapt the formula for the CompatibleProducts table but the result is not the expected one 

CompatibleProducts =

ADDCOLUMNS(

    SUMMARIZE('Parts with Variants', 'Parts with Variants'[Part]),

    "Compatible Products",

    CONCATENATEX(

        FILTER(

            SUMMARIZE(

                'Compatibility',

                'Compatibility'[Variant],

                'Compatibility'[Attribute],

                "IsCompatible",

                MAXX(

                    FILTER(

                        'Compatibility',

                        'Compatibility'[Value] = "Yes"

                    ),

                    'Compatibility'[Attribute]

                )

            ),

            NOT(ISBLANK([IsCompatible])) // Keep only those that are compatible

        ),

        [IsCompatible],

        "; "

    )

)

[Kedar_Pande]

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])
)