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

 

Table1 Parts with variants

PartVariant
11.1
11.2
11.3
22.1
33.1
33.2
44.1
55.1

 

Table 2: Compatibility

VariantABCDE
1.1YesTBD TBDYes
1.2 YesYes  
1.3     
2.1 Yes YesTBD
3.1  TBD  
3.2Yes   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:

PartCompatible ProductsTBD
1A; B; C; ED
2B; DE
3A; EC
4C 
5BD

 

 

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:
 
PartCompatible ProductsTBD
1  
1A; EB; D
1B; C 
2B; DE
3A; EC
3EC
4C 
5BD

 

Can someone please help me out? Thank you!

Need Help

5 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super 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])
    )
  • alinamarinbadea's avatar
    alinamarinbadea
    Frequent Visitor
    1 year ago

    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],
            "; "
        )
    )

     

     
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      alinamarinbadea
      Frequent Visitor
      1 year ago

      Thank you, Anonymous , 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)