Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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 )
@tamerj1
What I want is a table containing this product,because it was the only product bought by the 2 customers in the filter.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
I guess you wat to do that for any selected number of customers, could be 3 or more?
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
87 | |
87 | |
52 | |
37 | |
23 |