Search code examples
powerbipowerquery

Circular Dependency detected error in PBI, how to fix it?

I am running in to an error when I try to create some new columns.

This is the first one, which has worked very well:

I tried it this way, too:

And here is the second query (with the error included):

This makes no sense to me, and I am confused.

Any and all assistance would be greatly appreciated.

Here is my data visual table: I am trying to add a column which calculates the average percentages per country for values which are not 'Site Status = Terminated'.

Solution

  • Circular Dependency occurs in Calculated Columns due to row context transition. When using CALCULATE, behind the scenes it is adding every column as a filter for that row - this includes Calculated Columns. Therefore the second Calculated Column includes the first and vice-versa.

    To better understand the Circular Dependency in Calculated Columns see these two excellent posts:
    Circular Dependency between Calculated Columns in a Table in DAX
    Understanding circular dependencies in DAX

    Possible solutions

    1. Use ALLEXCEPT({Table}, {The column of interest}) or REMOVEFILTERS({all other calculated columns}). Example try:
    CALCULATE(
  ...,
  ALLEXCEPT('All Sites TP1', [Site Status]),
  'All Sites TP1'[Site Status] <> "Terminated"
)
    1. Use Measures instead of Calculated Columns in your case.