topic Circular dependency in DAX column in DAX Commands and Tips

Circular dependency in DAX column

Vinnie — Tue, 14 Feb 2023 09:34:28 GMT

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?

Re: Circular dependency in DAX column

Vinnie — Tue, 14 Feb 2023 11:52:01 GMT

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.

Re: Circular dependency in DAX column

Vinnie — Wed, 15 Feb 2023 09:38:41 GMT

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.

Re: Circular dependency in DAX column

Vinnie — Wed, 15 Feb 2023 12:37:14 GMT

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