Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Termenoque
Frequent Visitor

Circular Dependancy when creating Relationship

I have  various tables with dates and I am able to create relationships on dates to my calendar table all but one of the tables where I presented with a circular dependancy message. I am unable to see why there would be a circular reference as the two tables are not linked in any calculated colums etc and I have been able to connect to other tables in the exact same scenario.

 

Here is the error message

Termenoque_0-1647890218575.png

 

Here is my data model 

Termenoque_1-1647890249609.png

 

I've been scratching my head for days now and would appreciate any help/guidance from the community. Thanks in advance.

1 ACCEPTED SOLUTION

To demo...

 

bcdobbs_1-1647896047168.png

 

exactly the same model...

 

bcdobbs_2-1647896097785.png

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

7 REPLIES 7
bcdobbs
Super User
Super User

How is your date table built? Suspect you may be using CALENDARAUTO?



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Hi Ben, yes I am using CalendarAuto as I have several tables with dates that I need to use and didn't know how to get to a min and max date across various tables. I thought CalendarAuto might be causing this issue but not sure how to correct. Any ideas? Thanks

 

It's worth trying the folowing. When you create the relationship drag from the fact table (many side) into the date table. I think you'll find it works! I know I've had that issue before but now trying in latest version of Power BI I can't recreate the problem at all and it works both ways round.

 

However I tend to avoid using CALENDARAUTO and go for this sort of approach...

 

Date = 
    VAR EarliestYear = YEAR ( MIN ( Purchases[Date] ) )
    VAR LatestYear = YEAR ( MAX ( Purchases[Date] ) )

    RETURN
    
    ADDCOLUMNS (
        CALENDAR (
            DATE ( EarliestYear, 1, 1 ),
            DATE ( LatestYear, 12, 31 )
        ),
        "Month Year", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ) //Format as mmm YYYY 
    )

 

 

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Hi Ben, that is the approach I tried before but with so many dates in tables - all of which are relevant and with some being previous to my main table (main date field), so using min/max on the date field meant my calendar didn't have those earlier dates when having to reference the data for that using calendar table.

To demo...

 

bcdobbs_1-1647896047168.png

 

exactly the same model...

 

bcdobbs_2-1647896097785.png

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Why does this happen? I have the exact same problem, the tables are not related to each other but it gives me circular dependency

Thanks Ben - that done the trick. Why does it work when switching the direction in creating the relationship as the outcome is the same?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.