Forum Discussion
FIND ORDERS WITH SAME PRODUCTS
Hi guys, I want to create a simple measure that would simply count all the orders that share at least 1 of the same products of a selected order. For example. When I choose ORDER 2 which conta...
johnt75
Super User
Super UserPresuming that you have a one-to-many relationship from Products to Orders, you could try
Similar orders =
var thisOrderProducts = VALUES(Orders[Product ID])
return CALCULATE( DISTINCTCOUNT(Orders[Order ID]), REVOVEFILTERS(Orders),
TREATAS( thisOrderProducts, Products[Product ID]) )This works thank you ! Out of curiosity, is there a way to modify the above formula to take into account all selected products ?
So in order to get a count, the order must contain all the same products as the selected order
- johnt75
This might have performance issues if you have a lot of orders, but in principle you could try
Orders matching all products = var thisOrdersProducts = VALUES( Orders[Product ID]) var result = SUMX( ALL(Orders), var currentOrderProducts = VALUES( Orders[Product ID]) return IF( ISEMPTY( EXCEPT( thisOrdersProducts, currentOrderProducts) ), 1, 0 ) ) return result
