Circular Dependency when not using calculated function

[aJ2]

Hi everyone.

Initially after encountering this error, I figured out that using calculated was the main source of this problem, I took another route and then used 'IF' to get the values. 

But when I am using the two calculated columns to create another column, I encountered this error.

Following are the calculated columns:-

1. revenue gross = IF(order_items[is_item_gross], [revenue all], 0) // calculated column

2.  revenue net = IF(order_items[is_item_net], order_items[revenue gross], 0) // calculated column

3. rev progress = IF(order_items[is_item_in_progress], order_items[revenue net],0) // calculated column 

4. rev inprogress shipped = [% shipped2] * order_items[revenue in progress] //calculated column

5.  orders sold = IF(order_items[is_item_sold],order_items[order gross],0) // calculated column

6. order gross = IF(AND(order_items[orders all], order_items[is_item_gross]),1,0) // calculated column

7. 

% shipped2 =
var progress_order = CALCULATE([orders sold], order_items[is_item_in_progress], order_items[delivery_status]="Shipped", order_items[status]="Shipped")/*IF(AND(VALUES(order_items[is_item_in_progress]), AND(VALUES(order_items[is_item_gross]), VALUES(order_items[delivery_status])="Shipped")), [orders gross],0)*/
var in_progress_order = CALCULATE([orders sold], order_items[is_item_in_progress])/*IF(VALUES(order_items[is_item_in_progress]), [orders gross],0)*/

return (DIVIDE(progress_order,in_progress_order))// measure

8. rev progress not shipped = VALUE(order_items[rev progress]) - VALUE(order_items[rev inprogress shipped])// calculated column and error "A circular dependency was detected: order_items[rev inprogress shipped], order_items[rev progress not shipped], order_items[rev inprogress shipped]" // calculated column

Kindly guide.

Regards

aJ2

[v-chuncz-msft]

@aJ2,

You may take a look at the following articles.

• https://www.sqlbi.com/articles/understanding-circular-dependencies/
• https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

[aJ2 · Accepted solution]

@v-chuncz-msft,

Thanks for the reference.

I altered my approach. To calculate the column 'rev progress not shipped', I created local variables in the calculated column and then used the local variables in the calculation. 

rev progress not shipped = 

var progress_order = IF(AND(order_items[is_item_in_progress], sales_order_items[status]="Shipped"), sales_order_items[orders sold],0)

var in_progress_order = IF(order_items[is_item_in_progress], order_items[orders sold],0)

var shipped = DIVIDE(progress_order,in_progress_order)

var inprog_shipped = shipped * order_items[rev progress]

return (order_items[rev progress] - inprog_shipped)