A circular dependency was detected

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

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.