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