Forum Discussion

bstark1287's avatar
bstark1287
Helper II
3 years ago
Solved

Circular Dependency

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? 
 

 

Need Help
  • GilbertQ's avatar
    GilbertQ
    3 years ago

    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.

8 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    3 years ago

    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's avatar
      bstark1287
      Helper II
      3 years ago

      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's avatar
    GilbertQ
    Super User
    3 years ago

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

  • GilbertQ's avatar
    GilbertQ
    Super User
    3 years ago

    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's avatar
      bstark1287
      Helper II
      3 years ago

      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. 

       

       

  • bstark1287's avatar
    bstark1287
    Helper II
    3 years ago

    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.