circular depenency only in Power BI Service but not Power BI Desktop

[Babedgy]

Helloooo,

suddenly the automatic refresh of my report is failing since Power BI detects a circular dependency in my calender table.
Whats surprising is that when I open the report in Power BI desktop, everything is working fine, I can refresh the data and no error is detected. So the issue is only in Power BI service but not desktop.
I did no recent changes to the report, everything has been working fine for years and now suddenly it does not anymore.

Has there been a new update that is buggy or made some changes in Power BI service? I don't know how to solve the issue, since in Power BI desktop everything is working fine and I dont know where to look.

The same issue appeared at the same time for two of my clients. For both the reports have been working just fine for last couple of months.

this is the error that power Bi service shows to me.

Thank you for your help ❤️

[shpazkel]

I've resolved issue in my model: i had a couple relastions 1:1 with Both filtering, i've replaced it to 1:many with Both filtering, and it works.

[Starriver]

Hi all.

We have this problem as well, and raised a ticket with Micrsoft. We do have a call today in 20 minutes. I will let you know whats the outcome. They wrote that: "The issue can be mitigated by changing the CalendarAuto to Calendar function." which worked for me.

Here is what the support wrote me about this:

---------------------------------------------------------

As per your verbatim we understood that you are facing datasource error in multiple reports.

---

You could try the below suggestions:

• Currently few of our other users are facing a similar issue. An unexpected defect may have surfaced on our end which caused service outage in some regions. and our Product Group team also confirmed that it is a known issue, and they were actively working on this issue. 

• The issue can be mitigated by changing the CalendarAuto to Calendar function.

[Starriver · Accepted solution]

The support confirmed that the Product Group team from MSF is working on it. Using the Calendar function instead of CalendarAuto solves the problem as a temporary fix.

[Babedgy]

Ah, perfect. This actually worked in my reports as well. Thank yo so much for this 🙂

[Anonymous]

Hi, @Babedgy 

Team feedback that the fix has been deployed, do you still have this circular dependency issue?

Best Regards

Yongkang Hua

[GilbertQ]

Hi @Starriver 

This is the reason for things breaking Troubleshoot refresh scenarios - Power BI | Microsoft Learn

I do believe a fix is being rolled out but could take a few days.

[pmargari]

Hello all,
I had the exactly the same issue with several models with this error , after some investigation I found that the ones I had errors I had a table connected to the Date table with :


Set all cases to :



and solve all cases where I had this issue 
Hope works for you as well 
kr
Paulo

[SimonsJourney]

Hi Paulo,

thanks, that worked for me!

Same problem existed with an 1:1 relationship in my case. Had to switch it to Many to one and single cross filter direction. Not nice, but works.

Kind regards

Simon

[AndrejZitnay]

This didn't helped.

In one of the model I don't have date table at all.

Thanks anyway

[SamWiseOwl]

Excellent solution, thank you for sharing!

@Babedgy 

[SimonsJourney]

Hi all,

thanks for this post! 

We are having the same problem. It all worked fine until yesterday, October 13th.

We also use the CALENDARAUTO-function to create a date table. 

Automatic date/time is turned off globally.

Problem only occurs for one workspace for the date column. In other workspaces with same kind of date table it all works fine.

Further it does not only occur for the latest version of the problematic report, but also for quite old published reports.

It all works fine in Power BI desktop. Also none of the popular problems for circular dependencies does exist in the report. To figure out popular problems I referred to this article https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

Appreciate every possible solution steps 🙂

Kind regards

Simon

[AndrejZitnay]

Usually I have date table or cost centre tables and relationships between them.

CostCentreTable =
FILTER (
DISTINCT (
UNION ( VALUES(Table1[CostCentre]),VALUES(Table2[CostCentre]),VALUES(Table3[CostCentre]))
),
ISBLANK([CostCentre])=FALSE()
)

[SamWiseOwl]

Hi @Babedgy 

I found this issue when I had a Calendar Table that I had made was interfering with the built in calendar tables.

If you have not used the built in calendar tables (the ones that split your dates into y/q/m/d) then you can turn these off.

You can turn them off Globally for all future reports or turn them off for the current file only.

Specifically I had connected to the UK Gov Bank Holiday API and joined it to my Calendar table.

This was the specific issue for my refresh, when ever the join was recreated it broke.

In fact this error would occur when I dragged the bank holiday date onto the calendar table but not the other way around. Fun!

[Babedgy]

Heey @SamWiseOwl,

thank you so much for your reply.
I'm not sure I understand your answer correcly but I use a measure to create my calendar table. Its the same in all of my reports and works fine for other reports. Its only in my sales dashboards that this table suddenly blocks the automatic refresh because of a circular dependency. But only in Power BI Service, not in Power BI Desktop. It's very strange.

I'm not sure if you can understand the measure since its in german but still here it is:

Kalendertabelle =

VAR BasisKalender =

    CALENDARAUTO(12) //Gibt an, dass das Jahr im Dezember endet

RETURN

    GENERATE( //Definieren der wichtigsten Variablen

        BasisKalender,

        VAR BasisDatum = [Date]

        VAR JahresDatum = YEAR(BasisDatum)

        VAR MonatsDatum = MONTH(BasisDatum)

        VAR QuartalsDatum = QUARTER(BasisDatum)

        VAR WochenDatum = WEEKNUM(BasisDatum, 21)

        VAR TagDatum = DAY (BasisDatum)

        RETURN ROW( //Definieren der Spalten

            "Referenzdatum", BasisDatum,

            "Jahr", JahresDatum,

            "Quartal", "Qrtl " & QuartalsDatum,

            "MonatsNr", MonatsDatum,

            "Monat", FORMAT(BasisDatum, "MMMM"),

            "Woche", "KW " & FORMAT(WochenDatum, "00"),

            "TagNr", TagDatum,

            "Wochentag", FORMAT(BasisDatum, "DDDD"),

            "Jahr.MonatsNr", JahresDatum & "." & FORMAT(BasisDatum, "MM"),

            "Monat Jahr", FORMAT(BasisDatum, "MMM YY")

        )

    )
It's very basic and nothing crazy. And it does work fine in other reports so I dont understand why not in this one.

Removing Auto doesnt help too unfortunately. 

Thank you grande

[AndrejZitnay]

Hello @SamWiseOwl ,

I have same issues. Removing Auto date doens't help.
We have this problem on reports with aggregate tables

Andrej

[SamWiseOwl]

Is yours with a Date column also or some other relationship?