Count if both products is bought

[Anonymous]

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

[CNENFRNL]

Correct modeling results in simple solution,

[Anonymous]

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? 

[Greg_Deckler]

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

[Anonymous]

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? 🙂

[Greg_Deckler · Accepted solution]

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

[Anonymous]

@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 🙂