Create Measure to Avoid Circular Dependency

[DataUsurper]

Hello All  o/

So, the backstory: We have determined approximately 40% of our orders have the pricing and totals zero'd out (whooooooole nother story), what we're wanting to do is create a calc to backfill those missing numbers with approximates values. I created a calc'd column to do so but am getting the circular dependency error. I created that column because I'm needing this applied to our line item detail records; or can this be made as a measure and still capture the right data/calcs?

My calc is a number of lookupvalue-logic-checks before calculating an average for our approximate price to backfill:

backfill = 
IF(

LOOKUPVALUE('tbl_Orders_Invd State'[IS_INVD],'tbl_Orders_Invd State'[s_OrderID_DAX],tbl_Orders_LineItemDetails[s_OrderID_DAX])="$0 Order" && 

LOOKUPVALUE('tbl_Orders_Invd State'[RENTAL_ITEMS],'tbl_Orders_Invd State'[s_OrderID_DAX],tbl_Orders_LineItemDetails[s_OrderID_DAX])>1 && 

LOOKUPVALUE('tbl_Orders_Invd State'[SHIPPED_ITEMS],'tbl_Orders_Invd State'[s_OrderID_DAX],tbl_Orders_LineItemDetails[s_OrderID_DAX])="Yes" &&

LOOKUPVALUE('tbl_Orders_Invd State'[RENTAL_ORDER],'tbl_Orders_Invd State'[s_OrderID_DAX],tbl_Orders_LineItemDetails[s_OrderID_DAX])="Yes" &&

tbl_Orders_LineItemDetails[SOURCE]="REV" && 

LOOKUPVALUE('key_ECODES.ECODE'[% Priced],'key_ECODES.ECODE'[ECODE.eCODE],tbl_Orders_LineItemDetails[ECODE.eCODE])>.5,

CALCULATE(AVERAGE(tbl_Orders_LineItemDetails[ACTUAL_PRICE]),tbl_Orders_LineItemDetails[ACTUAL_PRICE]>0,YEAR(tbl_Orders_LineItemDetails[SHIP_DATE])=YEAR(tbl_Orders_LineItemDetails[SHIP_DATE])),

tbl_Orders_LineItemDetails[ITEMTOTAL])

 Spacing added for legibility's sake.

Coulple screenies: (1) Measure w/ error & (2) example of line item records

Measure w/ errorExample of line item detail data

Thank you kindly for any assistance.

DU

[V-lianl-msft]

Hi @DataUsurper ,

If the calculation of the result you want involves circular iteration, DAX cannot solve this kind of problem for the time being. It is recommended that you use Python or R to solve the problem.

Could you share the sample pbix through OneDrive for Business to see if we can avoid circular dependency.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataUsurper]

top of page bump