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 contains PRODUCT A (PA) , PRODUCT B (PB)
I want it to be able to count the orders highlighted in green (shown below)
This is my calculation but I cannot get it to work. Any help would be appreciated.
4 Replies
Presuming 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
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
# Intersecting Orders = if( HASONEFILTER( Orders[Order] ), var currentOrder = SELECTEDVALUE( Orders[Order] ) var RelevantProds = DISTINCT( Orders[Product] ) var relevantOrderCount = CALCULATE( DISTINCTCOUNT( Orders[Order] ), Orders[Order] <> currentOrder, RelevantProds, ALL( Orders ) ) return relevantOrderCount )Without knowing what the model looks like it's difficult to give a definite answer to this question. Also, we don't know if choosing an order should run the calculation against all the orders or only some subset of them. The formula above runs it for all orders.
