Total number of buyers that only purchased with particular seller

[rigelng1995]

Hi there

I am stuck with this dax measure and can't seem to wrap my head around it. 

I have a table with 3 columns (Althought the last one doesn't really matter)  

I would like to know the total number of buyers that only purchase with a certain seller

My Table

Seller	Buyer	Sales
a	1	100
a	2	100
a	3	100
a	4	100
a	4	100
a	6	100
a	6	100
a	6	100
b	1	100
b	3	100
b	1	100
b	3	100
b	2	100
b	2	100
c	1	100
c	2	100
c	3	100
c	4	100
c	1	100
c	5	100

I think the middle step would be to group this table by the buyers and find the distinct seller count but I am not sure how to get to the next step 

group by buyer
	Distinct SellerCount
1	3
2	3
3	3
4	2
5	1
6	1

Here we know that buyer 5 and 6 only purchased with 1 distinct seller (Can be multiple purchases)

Final Result wanted - (A measure that calculated the total number of buyers that only purchase with them)

Seller	Total number of buyers that only purchase with them
a	1
b	0
c	1

The real table is around 20m rows with 500k distinct buyer and 2000 distinct seller.

Any help is appreciated

[v-jayw-msft · Accepted solution]

Hi @rigelng1995,

Please check following measures and see if the result achieve your expectation:

Measure = CALCULATE(DISTINCTCOUNT('Table'[Seller]),FILTER(ALL('Table'),'Table'[Buyer] = MAX('Table'[Buyer])))

Measure 2 = CALCULATE(DISTINCTCOUNT('Table'[Buyer]),FILTER(ALL('Table'),[Measure] = 1&&'Table'[Seller] = MAX('Table'[Seller])))

Result would be shown as below:

BTW, Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[rigelng1995]

I ran it against my dataset - I think conditioning on distinctcount is too memory intensive for 20m rows. 

But tested against a subset and it works well. Thanks a lot for the help.