Forum Discussion

siddhantk989's avatar
siddhantk989
Icon for Helper III rankHelper III
9 years ago
Solved

"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

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • malagari's avatar
    malagari
    9 years ago

    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.

9 Replies

  • malagari's avatar
    malagari
    Icon for Continued Contributor rankContinued Contributor
    9 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

    • Habibi's avatar
      Habibi
      Frequent Visitor
      2 years ago

      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??

  • amirabedhiafi's avatar
    amirabedhiafi
    Icon for Impactful Individual rankImpactful Individual
    5 years ago

    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's avatar
      Amrita_Biswas
      New Member
      4 years ago

      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 ?

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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