Getting circular dependency error while creating calculated column

[amikm]

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?

[v-angzheng-msft]

HI, @amikm 

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

[amikm]

Any update

[PaulDBrown]

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

[amikm]

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.