Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Impactful Individual
Impactful Individual

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.