topic Re: Count if both products is bought in DAX Commands and Tips

Count if both products is bought

Anonymous — Sat, 21 Aug 2021 11:11:18 GMT

Hi,

Need some Dax help.

Trying to calculate how many products is bought of all customers only if they have bought all chosen products.

If I filter in a slicer on Product "XX" AND "YY" a simple count measure would give me the result "6"

But as said I only want it to count the products if the same customer bought both products chosen in the slicer. So the result should be "4", As only customer A and B bought both products.

Real data is larger with more products and customers and the amount of products chosen can be more than two.

Datatable simplyfied:

Customer	Product
A	XX
A	YY
B	XX
B	YY
C	XX
C	ZZ
D	YY

Re: Count if both products is bought

Greg_Deckler — Sat, 21 Aug 2021 14:06:35 GMT

Anonymous I wrote something similar to this Better Together - Microsoft Power BI Community.

But, I think in your case:

Measure Count = VAR __Values = DISTINCT('Slicer'[Product]) VAR __Table = DISTINCT( FILTER( SELECTCOLUMNS('Datatable',"__Customer",[Customer],"__Product",[Product]), [__Product] IN __Values ) ) VAR __Table1 = GROUPBY(__Table,[__Customer],"__Count",COUNTX(CURRENTGROUP(),[__Product])) RETURN COUNTROWS(FILTER(__Table1,[__Count] = COUNTROWS(__Values))) * COUNTROWS(__Values)

Re: Count if both products is bought

CNENFRNL — Sat, 21 Aug 2021 18:29:21 GMT

Correct modeling results in simple solution,

Re: Count if both products is bought

Anonymous — Sat, 21 Aug 2021 19:01:57 GMT

Hi Again,

Thanks for your quick help - really appreciated 🙂

I think you are almost there.

Only thing is that it shouldn't be a distinct count.

From your shown dataset the answer should "4", as customer C&D (who bought both products) in total bought 4 products.

How to change?

Re: Count if both products is bought

Anonymous — Sat, 21 Aug 2021 19:08:58 GMT

Hi Greg,

Thanks a lot for your help - really great..

Maybe you are not far away from the result i'm searching for.

Only thing is that your measure dosen't seem to count more of the same products per Customer.

If the same customer bought more than one of the same product it needs to be counting all of them and not only once.

Does it makes sence? 🙂

Re: Count if both products is bought

Greg_Deckler — Sat, 21 Aug 2021 19:19:36 GMT

Anonymous Maybe. Try:

Measure Count = VAR __Values = DISTINCT('Slicer'[Product]) VAR __Table = DISTINCT( FILTER( SELECTCOLUMNS('Datatable',"__Customer",[Customer],"__Product",[Product]), [__Product] IN __Values ) ) VAR __Table1 = GROUPBY(__Table,[__Customer],"__Count",COUNTX(CURRENTGROUP(),[__Product])) VAR __Table2 = SELECTCOLUMNS(FILTER(__Table1,[__Count] = COUNTROWS(__Values)),"__Customer",[__Customer]) RETURN COUNTROWS(FILTER(Datatable,[Customer] IN __Table2 && [Product] IN __Values))

Re: Count if both products is bought

Anonymous — Sun, 22 Aug 2021 08:11:27 GMT

Greg_Deckler - You are the man and came first to the finish line.

Thanks for your help - much appreciated 😁😁

Now I'll take time to read your Better Together Topic 🙂