Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
alinamarinbadea
Frequent Visitor

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!

5 REPLIES 5
v-linhuizh-msft
Community Support
Community Support

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:

vlinhuizhmsft_0-1728375581438.png

 

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

vlinhuizhmsft_1-1728375813417.png

 

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

'Compatibility'[UpdatedTBD]:

vlinhuizhmsft_2-1728375864367.png

vlinhuizhmsft_4-1728376024779.png

 

4.The final result is as follows:

vlinhuizhmsft_6-1728376183433.png

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.

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 ?

alinamarinbadea_0-1728892460322.png

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

Can anyone assist here please?

alinamarinbadea
Frequent Visitor

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],
        "; "
    )
)
alinamarinbadea_0-1728304774899.png

 

 
Kedar_Pande
Super User
Super User

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.