Forum Discussion

brennahurley's avatar
brennahurley
Frequent Visitor
1 year ago
Solved

Circular Dependency Error but no dependency

I have multiple different units with different unit numbers. The status of the unit is changed and recorded in a status log along with the date of the change. That is the start date of the status fo...
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    this table definition looks benign. Have you marked it as a Date table?

     

    Anyway, since dates are immutable it is always better to use a calendar table based on a precomputed source (like an Excel file on a sharepoint)

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi brennahurley,

     

    Just checking in to see if the suggestions shared by lbendlin  and Deku  helped in resolving the issue with calculating the end date of a unit’s status using DAX. As you encountered a circular dependency error, this usually occurs when a calculated column attempts to reference values that are also being calculated row by row within the same column.

    If any of the responses by community members addressed your concern, please consider marking perticular response as the Accepted Solution. Feel free to reach out if you need further assistance or clarification.

     

    Best regards,
    Prasanna Kumar

Deku's avatar
Deku
Super User
1 year ago

'DimStatusLog'[Full House Number] = fullHouseNumber

 

Is syntactic sugar for

 

Filter( all( 'DimStatusLog'[Full House Number] ),  'DimStatusLog'[Full House Number] = fullHouseNumber)

 

You can replace this with allnoblankrow to avoid consider the blank row consideration and therefore the circular reference

 

Filter( allnoblankrow( 'DimStatusLog'[Full House Number] ),  'DimStatusLog'[Full House Number] = fullHouseNumber)

 

You need to do the same with the date filter.

 

This article explains https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/#:~:text=If%20your%20code%20looks%20correct,column%20or%20a%20calculated%20table.

brennahurley's avatar
brennahurley
Frequent Visitor
1 year ago

I tried this with the following code and got a simmilar error to before:

End Date =
var fullHouseNumber = [Full House Number]
var startDate = [Start Date]
return calculate(
    MIN('DimStatusLog'[Start Date]),
    Filter( allnoblankrow( 'DimStatusLog'[Full House Number] ),  'DimStatusLog'[Full House Number] = fullHouseNumber),
    Filter( allnoblankrow( 'DimStatusLog'[Start Date] ),  'DimStatusLog'[Start Date] > startDate)
)
 
error message: A circular dependency was detected: DimStatusLog[End Date], DimStatusLog[Days in Status], DimStatusLog[End Date].
([Days in Status] is simply calculated as [End Date] - [Start Date])

 

  • Deku's avatar
    Deku
    Super User
    1 year ago

    With a pbix file hard to debug. I would suggest moving the calculated column to powerquery