Circular dependency in DAX column

[Vinnie]

Hi all,

I need help with a DAX column. Originally the column worked just fine but when I added some extra logic I got an error (circular dependency).

I am still trying to understand what a circular dependency is. From my knowlegde all columns in a table depend on eachother. And I think it is due to the fact that I am now calling a column in my DAX formula from the same table.

Since I can't share the .pbix and details from my DAX formula I will try to write down the formula without the details in it. This is the DAX formula for the DAX column, added to the table 'File'.

DateMovedOutWorkingstock = 
var countofrowsinsubstatus = countrows(relatedtable('FileSubStatus'))
var countofrowsinstatus = countrows(relatedtable('FileStatus'))
var dateinworkingstock = calculate(selectedvalue(File[DateInWorkingStock])) 

return if(countofrowsinstatus = 0, dateinworkingstock, 
            if(countofrowsinsubstatus = 0, dateinworkingstock,
calculate(COALESCE(
    calculate(max('FileSubStatus'[StatusStartDate]),'FileSubStatus'[StatusId] = 3),  
    calculate(max('FileSubStatus'[StatusStartDate]),'FileSubStatus'[StatusId] = 49), 
    calculate(max('FileSubStatus'[StatusStartDate]),'FileSubStatus'[StatusId] = 48), 
    calculate(max('FileSubStatus'[StatusStartDate]),'FileSubStatus'[StatusId] = 8),  
    calculate(max('FileSubStatus'[StatusStartDate]),'FileSubStatus'[StatusId] = 11), 
    calculate(max('FileSubStatus'[StatusStartDate]),'FileSubStatus'[StatusId] = 12), 
    calculate(max('FileSubStatus'[StatusStartDate]),'FileSubStatus'[StatusId] = 66), 
    calculate(max('FileSubStatus'[StatusStartDate]),'FileSubStatus'[StatusId] = 57), 
    calculate(max('FileStatus'[StatusStartDate]), 'FileStatus'[StatusId] = 3), 
    calculate(max('FileStatus'[StatusStartDate]), 'FileStatus'[StatusId] = 49), 
    calculate(max('FileStatus'[StatusStartDate]), 'FileStatus'[StatusId] = 48), 
    calculate(max('FileStatus'[StatusStartDate]), 'FileStatus'[StatusId] = 66), 
    calculate(max('FileStatus'[StatusStartDate]), 'FileStatus'[StatusId] = 57), 
    calculate(max('FileStatus'[StatusStartDate]), 'FileStatus'[StatusId] = 61), 
    calculate(max('FileStatus'[StatusStartDate]), 'FileStatus'[StatusId] = 56), 
    calculate(max('FileStatus'[StatusStartDate]), 'FileStatus'[StatusId] = 109), 
    calculate(max('FileStatus'[StatusStartDate]), 'FileStatus'[StatusId] = 4) 
    
))))

In the table 'File' there are two columns: DateInWorkingStock and this new column DateMovedOutWorkingstock. The purpose of these columns is to determine, based on either the FileStatus or FileSubStatus when it moved out the working stock (hope I translated this correctly to English.

When adding this column I get a circular dependency error. Anyone know what I am doing wrong here?

[Vinnie · Accepted solution]

I fixed this issue by adjusting the DAX statement for the DAX column DateMovedOutWorkingStock.

I replaced 

var dateinworkingstock = calculate(selectedvalue(File[DateInWorkingStock])) 

by:

var dateinworkingstock = LOOKUPVALUE(File[DateInWorkingStock], File[ID], File[ID])

[Vinnie]

Please let me know if I need to provide any needed information. I would of course want to fix it but also want to understand why I am getting this error.

I do see now that when I remove either the 'Working stock' DAX table OR remove the relationship between the File table and the 'Working stock' table, the DAX column does not give an error.

[Vinnie]

I do think it might have to do with a calculated table I have in my datamodel.

The exact error of the circular dependency mentions this table as well, as the 'File' table.

This calculated column is a seperate table and the DAX code is as follows:

Working stock = 

GENERATE(ALLNOBLANKROW(‘Calender Workingstock’[Date]),
CALCULATETABLE(
var measuredate = SELECTEDVALUE(‘Calender Workingstock’[Date]) 
var temptable = 
filter(File, File[DateInWorkingStock] <= meetdatum &&
(File[DateMovedOutWorkingStock] > meetdatum || isblank(File[DateMovedOutWorkingStock]))) 

return selectcolumns(temptabel, "ID File", File[id File])
)
)

The DAX column DateMovedOutWorkingStock does not give an error when I remove this DAX table. But of course that is not a solution.