cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amikm
Helper III
Helper III

Getting circular dependency error while creating calculated column

Hi,

 

I have a measure in my Sales table :

WW_Anchor_R6Sales:=
var R6Sales=CALCULATE([MeasureR6Sales]>0, FILTER(Product,Product[Program] = "WW"
&& Product[Anchor] = "Y"))

return
IF(ISBLANK(R6Sales),0,R6Sales)

 

LY_Anchor_R6Sales:=
var R6Sales=CALCULATE([MeasureR6Sales]>0, FILTER(Product,Product[Program] = "LY"
&& Product[Anchor] = "Y"))

return
IF(ISBLANK(R6Sales),0,R6Sales)



MeasureR6Sales:=
var salesR6=CALCULATE(SUM(Sales[SalesAmount]),FILTER(Sales,Sales[InvoiceDate]>=EDATE([LastClosedMonth],-5) && Sales[InvoiceDate]<=[LastClosedMonth] && Sales[SalesAmount]>0))
return IF(ISBLANK(salesR6),0,salesR6)

 

When I have created a calculated column:

WW_Anchor_R6:= IF([WW_Anchor_R6Sales]>0,"Yes","No")


I am able to create, but when I am trying to create another calculated column say:

CalculatedColumn1:= IF([LY_Anchor_R6Sales]>0, "Yes", "No"), it is giving me an error saying that

 

Failed to save modifications to the server. Error returned: 'A circular dependency was detected: Sales[Calculated Column 1], Sales[WW_Anchor_R6], Sales[Calculated Column 1].

 

How to fix this error?

 

4 REPLIES 4
v-angzheng-msft
Community Support
Community Support

HI, @amikm 

 

It may be helpful if you create a copy of the table or to summarize a new table.

amikm
Helper III
Helper III

Any update

PaulDBrown
Super User
Super User

This video from the gurus at sqlbi.com will help you solve the issue:





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi @PaulDBrown ,

 

I have followed his videos, I am able to understand the reasons for circular dependency, but when I am trying to implement ALLEXCEPT in my use case, It is not allowing me to do so, I am getting another error related to ALLEXECPT. In my use case, I am using Measure then I am using the measure result to calculate my columns with IF condition.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.