Find the number of unshared values based on filters

[QuangMC]

Hello everyone,

I am currently working on a table that has sales info as such :

Customer ID	Product ID	Quantity sold	Price	Date sold
1	AAA	2	1.50	date1
2	BBB	4	2.50	date2
3	CCC	1	3.50	date3
2	CCC	1	2.50	date4
4	DDD	3	1	date5
3	AAA	2	1.50	date6

What I need to do is get number of different products sold between two clients. For example, if i compare customer 2 and customer 3, it needs to return "1", since there is one product that one client doesn't buy. Much like saying "who buy's what, and who doesn't". It's not a "DISTINCTCOUNT(ProductID);customerID="2"-DISTINCTCOUNT(ProductID);customerID="3"" since i need the different values.

It's very similar to a "LEFT OUTER JOIN" in Power Query, followed by a simple count, but the thing is that I only work on one table (there are way too many customers, so I won't be creating one table for each of them). So I'm looking for a mesure (or another way).

Is it possible to do something like this "Get the number of DIFFERENT distinct products between two specific customers" ?

I just can't find the right function to work with here.

Please feel free to ask more details if i am not clear.

Ideas ?

Thanks in advance !

[kentyler]

You could use COUNTROWS(), if it finds a value then there is at least other product the customer buys.

Do the countrows on the sales table, filtered by the customer and if it returns more that 1 that's your answer.

[amitchandak]

You need use except, the input of except can come from summarize, addcolumns along with the filter , customer 1 and 2.

If you are only one slicer take max for 1st client and taken min 2nd client.

https://docs.microsoft.com/en-us/dax/except-function-dax

https://docs.microsoft.com/en-us/dax/summarize-function-dax

For more than one, some more logic required.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Greg_Deckler · Accepted solution]

So something like this? Attached a PBIX as well, Table3, Page3

Measure = 
    VAR __Cust1 = MAX('Customers1'[Customer ID])
    VAR __Cust2 = MAX(Customers2[Customer ID])
    VAR __Table = 
        DISTINCT(
            SELECTCOLUMNS(
                UNION(FILTER('Table3',[Customer ID] = __Cust1),FILTER('Table3',[Customer ID] = __Cust2)),
                "__ProductID",
                [Product ID]
            )
        )
    VAR __Products = DISTINCT('Table3'[Product ID])
RETURN
    CONCATENATEX(EXCEPT(__Products,__Table),[Product ID],",")

[QuangMC]

Hello @Greg_Deckler ,

This is very close to what i need indeed ! I can comprehend most of the formula, but would you mind explaining it a bit more what it tells PBI to do ? Like translating the operation so that I am sure that I interpret this the correct way ? 😅

Thank you for your quick answer !

@amitchandak thank you too for your answer !