Forum Discussion
Dealing with a Circular Dependency and Subsequent Error Message
Hi Community, recently started my journey into the world of PBI and I'm looking for some assistance from any passing experts...
I am encountering a circular dependency between two calculated fields:
I've done a bit of reading and figured to apply the "Payroll Responses" calculation within the "Rolling 3M Payroll NPS" calculation as such:
Rolling 3M Payroll NPS =
CALCULATE(
SUM('REF.ROLLING 3M NPS SCORES B'[Payroll Promoters])/SUM('REF.ROLLING 3M NPS SCORES B'[Payroll Promoters])
+SUM('REF.ROLLING 3M NPS SCORES B'[Payroll Passives])
+SUM('REF.ROLLING 3M NPS SCORES B'[Payroll Detractors])*100
-CALCULATE(SUM('REF.ROLLING 3M NPS SCORES B'[Payroll Detractors])/SUM('REF.ROLLING 3M NPS SCORES B'[Payroll Promoters])
+SUM('REF.ROLLING 3M NPS SCORES B'[Payroll Passives])
+SUM('REF.ROLLING 3M NPS SCORES B'[Payroll Detractors])*100),
ALLEXCEPT('REF.ROLLING 3M NPS SCORES B','REF.ROLLING 3M NPS SCORES B'[Target Month]))
I'm not 100% sure whether I'm on the right track with the fix, as every time I try to update the calculation - on a seperate fork/version, using prior versions of raw data etc - the error shows again and fails to apply the change.
Beyond this, I am somewhat lost as to other options. Any assistance would be greatly appreciated as this update is now past due.
Thanks in Advance,
Jay
Hi,
Improperly created relationships between tables may create circular dependencies during calculations.
You can view the following blog to learn more information. It explains in detail the issue of circular dependencies between calculated columns.
Circular Dependency between Calculated Columns in ... - Microsoft Fabric Community
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
JayTaylor8 , Based on what I got
Payroll Responses should be a measure
Try
Rolling 3M Payroll NPS =
CALCULATE(
(SUM('REF.ROLLING 3M NPS SCORES B'[Payroll Promoters]) / [Payroll Responses] * 100)
- (SUM('REF.ROLLING 3M NPS SCORES B'[Payroll Detractors]) / [Payroll Responses] * 100),
ALLEXCEPT('REF.ROLLING 3M NPS SCORES B', 'REF.ROLLING 3M NPS SCORES B'[Target Month])
)
Make sure Payroll Responses and Rolling 3M Payroll NPS do not reference each other directly or indirectly Hi ,
Based on the information, try to use measures instead of calculated columns.
You can view the following link to learn more information.
Solved: "A circular Dependency was detected" error while c... - Microsoft Fabric Community
Understanding circular dependencies in DAX - SQLBI
Solved: Circular dependency - Microsoft Fabric Community
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Strangely enough, it seems the issue was in the relationship between our "Calendar" and "Holidays" tables. We changed the relationship from one-to-one to many-to-one and the PBI instance is now capable of applying changes such as the above.
Again, thanks for the feedback
JayHi,
Improperly created relationships between tables may create circular dependencies during calculations.
You can view the following blog to learn more information. It explains in detail the issue of circular dependencies between calculated columns.
Circular Dependency between Calculated Columns in ... - Microsoft Fabric Community
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
