A circular dependency was detected

[PranavR]

I have a table SalesAnalysis, in which I have 4 columns - Year, Month, Day,WeekNo.
In this table I have 2 type - Monthly and Weekly.
For Monthly data I get values for Year, Month,Day columns, and for Weekly data I get values for Year, WeekNo.

I need to create a relationship between this table and Date master. 
I created a calculated column in the SalesAnalysis table for Monthly_Dt for monthly data and Weekly_Dt for weekly data and combined them in another calculated column Date, where I added the type filter.

DAX -

Monthly_Dt = IF(ISBLANK('SalesAnalysis'[Month]) || ISBLANK('SalesAnalysis'[Day]),BLANK(),DATE(VALUE('SalesAnalysis'[Jahr]),VALUE('SalesAnalysis'[Month]),VALUE('SalesAnalysis'[Day])))

Weekly_Dt = 

CALCULATE(max('Dim Calendar'[Date]),FILTER('Dim Calendar','Dim Calendar'[YearWeek]='SalesAnalysis'[YearWeek]))

Date = 

IF('SalesAnalysis'[Type]="Monthly", 'SalesAnalysis'[Monthly_Dt],IF('SalesAnalysis'[Type]="Weekly",'SalesAnalysis'[Weekly_Dt],BLANK()))

But when I'm trying to create a relationship, I'm getting below error - 

A circular dependency was detected: SalesAnalysis[Weekly_Dt], 2d4d65ad-ef12-2dd7-823a-
6b8a1e2b983f, SalesAnalysis[Date], SalesAnalysis[Weekly_Dt].

[v-menakakota]

Hi @PranavR ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[PranavR]

Hi @v-menakakota,
This doesn't resolve the problem. Our Date dimension is in DAX, so it's not available in Power Query.

[v-menakakota]

Hi @PranavR ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster. 

Thank you. 

[v-menakakota · Accepted solution]

Hi @PranavR ,
Thanks for reaching out to the Microsoft fabric community forum. 

Please go through the below documents which may help you in resolving the issue:

FIX CIRCULAR DEPENDENCY ERROR / 2 Common Scenarios and how to fix them / Beginners Guide to Power BI
Solved: Need Help in Resolving Circular Dependency - Microsoft Fabric Community
Solved: Circular dependency - Microsoft Fabric Community

If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

Best Regards, 
Menaka.
Community Support Team  

[v-menakakota]

Hi @PranavR ,

I wanted to check if you had the opportunity to review the information provided by @johnbasha33 . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution so other members can easily find it.

Thank you.

[v-menakakota]

Hi @PranavR ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[johnbasha33]

Recommended Fix: Use Power Query Instead of Calculated Columns

To avoid circular dependency and still get the desired Date, do this:

Option 1: Precalculate the Date in Power Query

1. Go to Power Query (Transform Data)

2. In the SalesAnalysis table:

   • Add a custom column like:

     powerquery
     = if [Type] = "Monthly" and [Month] <> null and [Day] <> null then
     #date([Year], [Month], [Day])
     else if [Type] = "Weekly" then
     null
     else
     null
     
     

     3. After that, merge with the Dim Calendar table on [YearWeek] (only for "Weekly" type rows)

     4. From that merged table, bring in the [Date] column

     5. Final step: create a single Date column that pulls either the monthly date or the merged weekly date

        This avoids using DAX for something that’s fundamentally a data transformation task.
        
        

        Option 2: Rebuild the Date Outside the Relationship Logic

        If you must use DAX:

        • Don't use CALCULATE(MAX(...)) that depends on the related table.

          Instead, consider bringing YearWeek-Date mapping as a lookup column into the SalesAnalysis table using Power Query or with a LOOKUPVALUE if small and not recursive:

Weekly_Dt = LOOKUPVALUE('Dim Calendar'[Date], 'Dim Calendar'[YearWeek], 'SalesAnalysis'[YearWeek])

LOOKUPVALUE is a scalar function and doesn't drag in the entire filter context, making it safe to use without triggering circular dependencies — in most cases.


          Did I answer your question? Mark my post as a solution! Appreciate your Kudos !@PranavR