Generate a common parts table from either a single table or two identical tables

[BennyH]

Hello All

Basically, we have a table of products names, product codes and all of their corresponding part numbers.

We want to have two sets of two slicers, one set for product one and another for product two so to compaore two products.

The set of slicers for the two selected products are products names and product codes.

We want to find all the common part numbers between the two products based on the product name and product codes.

Also, another table of not common parts.  So two tables, one of common parts and the other of unique numbers per product.

We have tried several ideas.  Even tried using two identical tables with one to many relationships for the product name, product codes and part numbes, still no success.  Have done tons of searching for solutions or anything at least close.  Even tried ChatGTP and Copliot with no luck.

So, the user would select Product One and a Product Code and the same for Product Two.

It would filter out two lists of Part Numbers and use something like intersect to find the common part numbers between the two products.  Then use the intersect to remove the common from the two list, leaving the unique or difference of part numbers.  Any help would be greatly appreciated.  Thank you

[johnt75]

Create 2 tables to use in the slicers, like

Product 1 =
SUMMARIZE ( 'Table', 'Table'[Product Name], 'Table'[Product Code] )

Product 2 =
SUMMARIZE ( 'Table', 'Table'[Product Name], 'Table'[Product Code] )

Don't create any relationships from these tables, leave them disconnected. You can then create 2 measures like

Part is Common =
VAR SelectedPart =
    SELECTEDVALUE ( 'Table'[Part Number] )
VAR Parts1 =
    CALCULATETABLE (
        VALUES ( 'Table'[Part Number] ),
        TREATAS ( VALUES ( 'Product 1'[Product Code] ), 'Table'[Product Code] ),
        REMOVEFILTERS ( 'Table'[Part Number] )
    )
VAR Parts2 =
    CALCULATETABLE (
        VALUES ( 'Table'[Part Number] ),
        TREATAS ( VALUES ( 'Product 2'[Product Code] ), 'Table'[Product Code] ),
        REMOVEFILTERS ( 'Table'[Part Number] )
    )
VAR CommonParts =
    INTERSECT ( Parts1, Parts2 )
VAR Result =
    IF ( SelectedPart IN CommonParts, 1 )
RETURN
    Result

and

Part is Unique =
VAR SelectedPart =
    SELECTEDVALUE ( 'Table'[Part Number] )
VAR Parts1 =
    CALCULATETABLE (
        VALUES ( 'Table'[Part Number] ),
        TREATAS ( VALUES ( 'Product 1'[Product Code] ), 'Table'[Product Code] ),
        REMOVEFILTERS ( 'Table'[Part Number] )
    )
VAR Parts2 =
    CALCULATETABLE (
        VALUES ( 'Table'[Part Number] ),
        TREATAS ( VALUES ( 'Product 2'[Product Code] ), 'Table'[Product Code] ),
        REMOVEFILTERS ( 'Table'[Part Number] )
    )
VAR CommonParts =
    INTERSECT ( Parts1, Parts2 )
VAR AllParts =
    ALL ( 'Table'[Part Number] )
VAR UniqueParts =
    EXCEPT ( AllParts, CommonParts )
VAR Result =
    IF ( SelectedPart IN UniqueParts, 1 )
RETURN
    Result

You can then use these measures as filters on the table visuals, set to show only when the value is 1.

[BennyH]

Thank you johnt75 for your resposne but it didn't seem to work.  I tried working it out with no success. 

[ryan_mayu]

could you pls provide some sample data and expected output?

[BennyH]

Thank you ryan mayu for you reply,

This is sample source data table, I duplicated it for Product 2 table;

Product 1
Product Name	Product Code	Part Number
A	1	10001
A	1	20150
A	2	35120
A	5	20150
B	1	10001
B	2	35120
B	1	98456
B	5	84562
C	1	69500
C	1	69500
C	1	10001
C	2	35120

Using slicers;

Product 1 slicers; Product Name = A & Product Code = 1,

Product 2 slicers; Product Name = B & Product Code = 1,
common part numbers expected results;

Product A & B Common Parts
Product Name	Product Code	Part Number
A	1	10001
B	1	10001

same slicers selection, unique part numbers expected results is;

Product A & B Unique Parts
Product Name	Product Code	Part Number
A	1	20150
B	1	98456

The results could just be the PART NUMBERS.

hope this helps, I'm still trying johnt75 reply code with no success yet.

as always any help is greatly appreciated.  I figured this woul dhave been a very simple task using FILTER to geenrate two tables of the two products part numebrs and then intersect the two for the common parts.  INTERSECT doesnt play nice with slicers. 

[ryan_mayu]

@BennyH 

pls see the attachment below

[BennyH]

THANK YOU VERY MUCH ryan_muya works great.  I had to change it slightly but it worked great and thank you for providing within the context of an actaul power bi.  I'm trying to work through it to take it one step further.   return the part numbers that are in product B and not in product C.

same tables samples

Product 1
Product Name	Product Code	Part Number
A	1	10001
A	1	20150
A	2	35120
A	5	20150
B	1	10001
B	2	35120
B	1	98456
B	5	84562
C	1	69500
C	1	69500
C	1	10001
C	2	35120

returns parts that are not in C that are in B

Product B & C Parts no part of C
Product Name	Product Code	Part Number
B	1	98456
B	5	84562

we got part of it in your previous anwser but I believe we want to just filter out product C out of the results.  I tried that but without success, I ended up with an empty list of the second table of the non common parts.  thank you again

[ryan_mayu]

it's because you filter product A in one filter and filter product B in another filter. Then it will compare product A and product B. 

could you pls update the expected output？ what value will you select in each filter and what's the expected output?

[BennyH]

Thank you for your reply.  selection is all Product B and all of Product C, expected output would be Product B - Product Codes 1 and 5, part Numbers 9845 and 84562 as shown in the image below.  This is filtering at the Product level and getting only the numbers that are exclsuive to product B.  Think of it as a next generation product and return parts are carry-over, excluding new parts for product C.

Thank you again, I which the DAX filter and INTESECT and UNION and EXCEPT would work for this, I have the slicers filter each creating two tables but when I try to intersect the two thats where if fails.  After Googling that and discovering this is as designed we're here.  But what you provided defnitely work.

[ryan_mayu · Accepted solution]

@BennyH 

This logic is different from the one in your first post. I have updated the DAX.

pls see the attachment below