cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
siddhantk989
Helper III
Helper III

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

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

1 ACCEPTED SOLUTION
malagari
Responsive Resident
Responsive Resident

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.

Dan Malagari
Consultant at Headspring

View solution in original post

8 REPLIES 8
Franzile
New Member

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
Solution Supplier
Solution Supplier

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.

Amira Bedhiafi
Full Stack Business Intelligence Consultant @Capgemini

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
Responsive Resident
Responsive Resident

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.

Dan Malagari
Consultant at Headspring
Anonymous
Not applicable

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. 😄

@malagari Thanks for the solution.

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

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.

Dan Malagari
Consultant at Headspring

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors