Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cdawidow2
Frequent Visitor

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)ORDERS.PNG

 

This is my calculation but I cannot get it to work.  Any help would be appreciated. 

 

code.PNG

 

4 REPLIES 4
daXtreme
Solution Sage
Solution Sage

# 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.

johnt75
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors