dax.do // Find products bought by same customers

[rks]

Hi Community,

I have a little DAX-problem:

EVALUATE
CALCULATETABLE (
SUMMARIZE (Sales, Sales[ProductKey], Sales[CustomerKey]),
Customer[CustomerKey] IN { 17447, 17419 }
)

This table on https://dax.do/W8SMqM1rHrnGbL/ shows that the Product 1715 was bought by exactly two customers. Both customers also bought different products. I want to find all products that were bought by these two customers together, so only 1715 from that list.

EVALUATE
CALCULATETABLE (
VALUES(Sales[ProductKey]),
Customer[CustomerKey] IN { 17447, 17419 }
)

This formula gives me an "OR" condition and finds all product bought by either customer 17447 OR 17419. But I want to find - like I said, all products by customer 17447 AND customer 17419.

Thank you guys!

[rks]

@tamerj1 exactly, the two customers are actually coming from a slicer and are arbitrary. Could be also 3 or 4. But @CNENFRNL 's answer would provide the desired result, if the 2 would be replaced by "COUNTROWS(Customer)"

[tamerj1]

@rks 
Indeed, @CNENFRNL's solution is brilliant!

[johnt75]

EVALUATE
VAR cust17447 =
    CALCULATETABLE (
        VALUES ( Sales[ProductKey] ),
        TREATAS ( { 17447 }, Customer[CustomerKey] )
    )
VAR cust17419 =
    CALCULATETABLE (
        VALUES ( Sales[ProductKey] ),
        TREATAS ( { 17419 }, Customer[CustomerKey] )
    )
RETURN
    INTERSECT ( cust17447, cust17419 )

[rks]

@tamerj1 
What I want is a table containing this product,because it was the only product bought by the  2 customers in the filter.

[CNENFRNL · Accepted solution]

[tamerj1]

@rks 

For 2 products its easy

EVALUATE
INTERSECT (
    CALCULATETABLE ( VALUES ( Sales[ProductKey] ), Customer[CustomerKey] = 17419 ),
    CALCULATETABLE ( VALUES ( Sales[ProductKey] ), Customer[CustomerKey] = 17447 )
)

 but to do that dynamically regardless of the whatever customers are selected and whatever number ofcustomers then it's another story   

[tamerj1]

@rks 

I guess you wat to do that for any selected number of customers, could be 3 or more?