Circular Dependency

[bstark1287]

I am trying to create a calculated column within my 'tbl_Calendar'. The dax I am using is:

OE USD = CALCULATE(

SUMX('Raw Order Data',[Net Value])

)

- CALCULATE(

SUMX('Raw Order Data',[Net Value]),

USERELATIONSHIP('tbl_Calendar'[Date],'Raw Order Data'[Order Cancelled Date])

)

I have an active relationship from 'Raw Order Data'[Create Date] -> 'tbl_Calendar'[Date] 

I have an inactive relationship from 'Raw Order Data'[Order Cancelled Date] -> 'tbl_Calendar'[Date]

I am receiving an error: A circular dependency was detected: tbl_Calendar[OE USD], tbl_Calendar[Orders Entered], tbl_Calendar[OE USD].

None of the fields used in my dax are calculated columns or measures. I am not calling the fields mentioned in the error (but those are calculated columns but I am not sure why that would matter since they are not part of my new calculated column). Why is this returning an error? 

[GilbertQ · Accepted solution]

Hi @bstark1287 

Why could you not create a measure for sales and a measure for cancelled orders.

Then you can subtract sales from cancelled.

When you put this into your visuals it will work it out automatically.

[bstark1287]

You are a hero! The measures worked. For some reason I thought that measures wouldn't work. For reference of others the measures I used are posted below. 

[GilbertQ]

Glad to hear I could help!

[bstark1287]

This takes care of the circular reference (picture 1) but does not total (picture 2). Basically what I am wanting it to do it sum the total net value of the calendar date that an order was created on (so if 4 orders for $1 were created on 1/1/22 then it would show $4 on 1/1/22) then subtract the total net value of cancelled orders on 1/1/22 (so if one order was cancelled on 1/1/22 for $2 it would subtract $2 from the $4). 

I have a similar calculated column that is working in [Orders Entered] (picture 3). 

1.

2.

3.

[GilbertQ]

Hi @bstark1287 

It might work like this

CALCULATE(
SUMX('Raw Order Data',[Net Value]),
ALLEXCEPT(tbl_Calendar[OE USD]),
USERELATIONSHIP('tbl_Calendar'[Date],'Raw Order Data'[Order Cancelled Date])
)

[bstark1287]

We are getting close! I used [Orders Entered] for my allexcept field because [OE USD] wasn't an option and [Orders Entered] is referenced in the error message as a cause for the circular reference. The first part of the dax statement seems to be working so now I just need to determine where to put the ALLEXCEPT in the second part I think. 

[GilbertQ]

What happens if you put them into 2 seperate measures do you still get the error?

[bstark1287]

Yes same error. I did some reading and found this https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/ Based on the link I believe I need to add an ALLEXCEPT to my dax I am just not sure where. The article states