Circular Dependency Error but no dependency

[brennahurley]

I have multiple different units with different unit numbers. The status of the unit is changed and recorded in a status log along with the date of the change. That is the start date of the status for that unit. I want to calculate the end date of that status for the unit, which is the start date of the next status. I figured I could simply look at all of the start dates associated with the unit number and find the smallest one that is after the start date. I used the following DAX ([Full House Number] is a calculated column from the imported columns [Unit Number] and [House Letter] because sometimes a unit is A or B. [Start Date] is not a calculated column, it is imported from a spreadsheet):

End Date =

var fullHouseNumber = [Full House Number]

var startDate = [Start Date]

return calculate(

    MIN(DimStatusLog[Start Date]),

    'DimStatusLog'[Full House Number] = fullHouseNumber,

    'DimStatusLog'[Start Date] > startDate

)

I got this error: A circular dependency was detected: DimStatusLog[End Date], 19adbbc9-9f5f-4a3c-aeb2-38093c3796af, DimStatusLog[End Date].

I don't see how the equation for [End Date] is referencing itself, help please!

[v-pgoloju]

Hi @brennahurley,

As we haven’t received any further updates and there are no outstanding queries at the moment, we’ll go ahead and close this thread for now. If you have any additional questions in the future, please don’t hesitate to start a new thread we’re always here to help.

Warm regards,
Prasanna Kumar

[v-pgoloju]

Hi @brennahurley,

Just checking in to see if the suggestions shared by @lbendlin  and @Deku  helped in resolving the issue with calculating the end date of a unit’s status using DAX. As you encountered a circular dependency error, this usually occurs when a calculated column attempts to reference values that are also being calculated row by row within the same column.

If any of the responses by community members addressed your concern, please consider marking perticular response as the Accepted Solution. Feel free to reach out if you need further assistance or clarification.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @brennahurley,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[lbendlin]

Don't use CALENDARAUTO.  Use an external reference table for your calendar table.

[brennahurley]

Where did I use CALANDARAUTO? I don't understand what you mean by using an external reference table for my calendar table. I have [Start Date] connected to a date table that is calculated as follows:

DimStatusDate =

var startDate = DATE(2015,1,1)

var endDate = date(year(TODAY()),12,31)

return

    ADDCOLUMNs(

        CALENDAR(StartDate, EndDate),

        "Year", YEAR([Date]),

        "Month", MONTH([Date])

    )

[lbendlin]

this table definition looks benign. Have you marked it as a Date table?

Anyway, since dates are immutable it is always better to use a calendar table based on a precomputed source (like an Excel file on a sharepoint)

[Deku]

'DimStatusLog'[Full House Number] = fullHouseNumber

Is syntactic sugar for

Filter( all( 'DimStatusLog'[Full House Number] ),  'DimStatusLog'[Full House Number] = fullHouseNumber)

You can replace this with allnoblankrow to avoid consider the blank row consideration and therefore the circular reference

Filter( allnoblankrow( 'DimStatusLog'[Full House Number] ),  'DimStatusLog'[Full House Number] = fullHouseNumber)

You need to do the same with the date filter.

This article explains https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/#:~:text=If%20your%20code%....

[brennahurley]

I tried this with the following code and got a simmilar error to before:

End Date =

var fullHouseNumber = [Full House Number]

var startDate = [Start Date]

return calculate(

    MIN('DimStatusLog'[Start Date]),

    Filter( allnoblankrow( 'DimStatusLog'[Full House Number] ),  'DimStatusLog'[Full House Number] = fullHouseNumber),

    Filter( allnoblankrow( 'DimStatusLog'[Start Date] ),  'DimStatusLog'[Start Date] > startDate)

)

error message: A circular dependency was detected: DimStatusLog[End Date], DimStatusLog[Days in Status], DimStatusLog[End Date].

([Days in Status] is simply calculated as [End Date] - [Start Date])

[Deku]

With a pbix file hard to debug. I would suggest moving the calculated column to powerquery