Circular dependency detected error, but I cannot find the problem in my desktop file

[Young_G_Han]

  Dear Sir

I have uploaded a report and set a schedule refresh. I don't know how it happened, but the refresh suddenly failed due to the circular dependency detected error.

And I want to check my desktop file to fix it but I cannot find the measure or calculated columns in the tables, but there is no notice for the error.

The _Date table was created by calendarauto().

Can you help me how to find the reason?

The error message I got:

Data source error: A circular dependency was detected: _DATE[_DATE-434f9000-46e2-4730-80b3-586dbc4b859c], 0e14b7ce-1901-4fe8-af01-573ce867bc4a, _DATE[Date], _DATE[_DATE-434f9000-46e2-4730-80b3-586dbc4b859c].
Cluster URI: WABI-WEST-EUROPE-redirect.analysis.windows.net
Activity ID: 929cb4c7-079c-4230-b6ed-c93465e506b6
Request ID: 8eeffba7-e596-1f1e-bbd0-80b69c77cbfa
Time: 2024-10-16 02:07:57Z

Regards,

Young.

[ScottGunsaullus]

Yes, I am getting a similar error on a scheduled refresh.  Error message points to the CalendarAuto table.  I recall reading that CalendarAuto could be problematic but had not experienced it directly.  The thing is, the model had been stable.  I uploaded it some time ago.  Since then, the scheduled refresh had completed without errors, until this week.  My first thought was "why is this happening now?".  Next, "what has changed?"  Now I recall that the function actually analyzes the date fields in the model of MIN and MAX values.  There must be some new values in my data that are leading to this result.

[Young_G_Han]

I found the reason while I solve the problem. I was using the data column and the time column seperately. That caused the circular dependecies in my case.

I used the following measure for the date table.

CALENDAR(

    DATE(YEAR(TODAY()) - 11,1,1),

    DATE(YEAR(TODAY()) + 1,12,31)

)

[AlejandroVazque]

I have the same problem but I'm using the CALENDARAUTO(3) function as my fiscal year is from April to March. I do have a lot of reports and tables linked to my calendar table so prefer not to have to redesign all the relationships (more than 500 relationshisps)

[Young_G_Han]

I found the reason while I solve the problem. I was using the data column and the time column seperately. That caused the circular dependecies in my case.

I used the following measure for the date table.

CALENDAR(

    DATE(YEAR(TODAY()) - 11,1,1),

    DATE(YEAR(TODAY()) + 1,12,31)

)

[Young_G_Han]

Thank you Sir! It is working, the Calendarauto() had an error. With your example, I could solve the problem.

Have a wonderful day!

[rajendraongole1 · Accepted solution]

Hi @Young_G_Han - you may want to consider rebuilding the _Date table without the CALENDARAUTO() function. Instead, use a specific date range with the CALENDAR() function, which can help you manually control the date range and avoid potential conflicts.

eg: _Date = CALENDAR(DATE(2020,1,1), DATE(2024,12,31))

You can use a simpler function like CALENDAR() if you don't need the dynamic nature of CALENDARAUTO().

you can also cross check In Power BI Desktop, you can use the Query Dependencies View to visualize relationships between your queries. This can help you identify where the circular dependency might be occurring.

cross check the model view and connections.