Avoiding circular dependencies in calculated columns

[Anonymous]

Hi all! I have a calculated column which I want to use in another calculated column.

My first calc column is below and sits in the "Date Table":

doesExist? = CALCULATE( if(ISBLANK(sum('Store'[DailyProfit]) ),"No","Yes"),CROSSFILTER('Date Table'[Datekey],'Store'[Date],Both))

Here I am checking to see when the last day a value was recorded in the "DailyProfit" column of another table "Store". A "Yes" is shown if a value was recorded on a specific date and "No" if not.

The second calc column also in "Date Table":

Target = 
var _1 = maxx(filter('Date Table',[isL7D?]="Yes"),'Date Table'[Datekey])
return
if('Date Table'[Datekey] >=_1-7 && 'Date Table'[Datekey]<=_1,"Yes","No")

Here I am trying to add "Yes" if the there is a "Yes" in the "doesExist?" column and is within the last 7 days of the last "Yes" (i.e. is within the last 7 days of a value being recorded in the "Store"[DailyProfit] column).

However, I receive the following error 

A circular dependency was detected: Date Table[doesExist?], Date Table[column], Date Table[doesExist?].

For example data please see my previous question

[amitchandak]

@Anonymous , refer if this can help

https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

[Anonymous]

hi @amitchandak, thank you for the resource. Whilst it was interesting I don't believe it helped with my problem as I can't seem to incorporate their solutions into my problem.

[dax · Accepted solution]

Hi @Anonymous , 

You could try below expression to see whether it work or not.

Target = 
var _1 = maxx(filter('Date Table',RELATED('Table'[Value])<>BLANK()),'Date Table'[Datekey])
return
if('Date Table'[Datekey] >=_1-7 && 'Date Table'[Datekey]<=_1,"Yes","No")

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @dax that's it thank you! I had to slightly modify it as I had a one-to-many relationship between my Date Table and Values Table. So the DAX looked like this in the end:

Target = 
var _1 = maxx(filter('Date Table',sumx(RELATEDTABLE('Table'),'Table'[Value])<>BLANK(), 'Date Table'[Datekey])
return
if('Date Table'[Datekey] >=_1-6 && 'Date Table'[Datekey]<=_1,"Yes","No")