How is this NOT throwing a circular reference error?

[dwoolaver75]

Hello, I am trying duplicate the logic in these two formulas in either SQL PBI power query. As I look at the excel formulas I don't under how excel is NOT throwing a circular reference error. My efforts to duplicate these two column in power query and PBI/DAX are giving me circular reference errors (as I would expect)

Sums= =IF(LEFT([@REFTYPE],6)="Invent",SUMIFS([Sales Orders],[ITEMID],[@ITEMID],[REQDATE],"<"&[@[FINAL Best Before Date]])+SUMIFS([Forecast],[ITEMID],[@ITEMID],[REQDATE],"<="&[@[FINAL Best Before Date]])-SUMIFS([Quantity Allocation],[ITEMID],[@ITEMID],[FINAL Best Before Date],"<"&[@[FINAL Best Before Date]]),"")

Quantity Allocation=IF(LEFT([@REFTYPE],6)="Invent",IF([@Sums]>[@[On Hand]],[@[On Hand]],[@Sums]),"")

 

[p45cal]

What have you got here?:


and if you toggle it do you get a circular refererence error thrown?

 

[dwoolaver75]

My setting for iterative calc was OFF. (although I see it could be related here) turning it on had no effect.

It is probably worth noting that the table shown in the spreadsheet is a external data source. Which in turn is a cobbled together power query with several different sources. The formulas however are seemingly in the excel table and not the power query data model. Interesting I pasted values only from the table into a new blank workbook and then tried to copy in the formulas. I then got a message about pasting active content and after allowing the paste I get a file dialog box indicating excel is looking for something. It does NOT appear to a macro.

The author of this file is no longer available. But what they are doing for "Sums" is adding up other rows in the same table for the same item with the SUMIF