Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bstark1287
Helper II
Helper II

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? 
 
bstark1287_0-1675373726794.pngbstark1287_1-1675373780173.png

 

1 ACCEPTED SOLUTION
GilbertQ
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

8 REPLIES 8
GilbertQ
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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. 

bstark1287_0-1675393993023.png

bstark1287_1-1675394067127.png

 

 

Glad to hear I could help!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

bstark1287
Helper II
Helper II

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.

bstark1287_0-1675378908215.png

2.

bstark1287_1-1675378933416.png

3.

bstark1287_2-1675379035942.png

 

GilbertQ
Super User
Super User

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])
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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_0-1675378207365.png

 

GilbertQ
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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 

bstark1287_0-1675376888409.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors