"A circular Dependency was detected" error while creating a calculated column

[siddhantk989]

Hi,

 I am trying to create a table visualization in Power BI. Where I am showing sales by category and target %. Now I am creating 2 calculated columns in it. The first column tells me Yearly Sales target. Which is as below:

 Yearly Sales Target = (1 + Target%) * [Yearly Sales].

This is working fine. But when I am trying to create a new column for monthly sales target with the formula as below:

Monthly Sales Target = (1 + Target%) * [Monthly Sales].

I am getting the error as "A circular Dependency was detected".

Any suggestions on how to remove this error?

Thanks,

Siddhant

[Franzile]

Hi,

I have encountered a circular depenndency in the following piece of code:

StationaryStart =

VAR varTimeMinInterval = 20

VAR varDistanceMeterInterval = 30

VAR varFutureDate =

'Location'[EventDateTime] + TIME ( 0, varTimeMinInterval, 0 )

VAR varMaxRow =

CALCULATE (

MAX ( Location[Row Number] ),

FILTER (

ALLEXCEPT ( 'Location', 'Location'[Device Id] ),

'Location'[EventDateTime] <= varFutureDate

)

)

VAR varDistance =

CALCULATE (

SUM(Location[Distance (m)]),

FILTER (

ALLEXCEPT ( 'Location', 'Location'[Device Id] ),

'Location'[EventDateTime] <= varFutureDate

)

)

RETURN

IF(varDistance <= varDistanceMeterInterval, varMaxRow, 0)

I am trying to return the row number for a distance travelled less than varDistanceMeterInterval with a minimum of varTimeMinInterval spent at the given location.

Hoping you can give some direction.

Kind regards,

Franzile

[amirabedhiafi]

The entities involved in dependencies are tables, columns, and relationships. Each of these objects might depend on other objects. For example, a table might depend on a relationship, a column might depend on a table, and so on.

These are the two basic rules (a third rule will come later):

• An expression depends on all the columns, tables and relationships used in the expression.
• A relationship depends on the columns used for the relationship itself.

Try to create measures instead of creating calculated columns.

[Amrita_Biswas]

I created meaures but needed to create a column as we cannot use measures directly in legend.

So what if I have to use the measures in a legend then how to use it ?

[malagari · Accepted solution]

This is a good refresher on circular dependencies: https://www.sqlbi.com/articles/understanding-circular-dependencies/.

In your case, Power BI does not allow to have two calculated columns that contain measures that are also based on that table.  In order to understand why, you'd need a better understanding of what's going on under the hood.

In order to get around it, you should try turning these into measures that reference the [Yearly Sales] and [Monthly Sales] measures.

[Habibi]

Hello, I have a similar issue. I'm working on a report to calculate the Opening Balance on a daily bases. after the expenses are deducted, we have a closing balance. so, for the day 2 opening balance, we want to use the day 1 closing balance. after all deductions for day 2 as well, we want the day 3 Opening balance to be the day 2 closing balance. e.t.c.

I'm not sure of a way to approach this, but our opening balance will have to be = yesterday's closing balance, and our closing balance is calculated as opening balance - expenses. the circular dependency error will surely occur.

Can anyone assist??

[Anonymous]

Thanks @malagari  that has saved me on a report i am doing.  Been pulling my hair out until i read that article, problem now solved. 😄

[siddhantk989]

@malagari Thanks for the solution.

[sgovindarajan]

@siddhantk989 @malagari I am facing the same issue. How did you solve this?

[siddhantk989]

Hi @sgovindarajan

 This solution from Dan actually helped me with the error:

This is a good refresher on circular dependencies: https://www.sqlbi.com/articles/understanding-circular-dependencies/.

In your case, Power BI does not allow to have two calculated columns that contain measures that are also based on that table.  In order to understand why, you'd need a better understanding of what's going on under the hood.

In order to get around it, you should try turning these into measures that reference the [Yearly Sales] and [Monthly Sales] measures.