Forum Discussion

rpinxt's avatar
rpinxt
Solution Sage
3 years ago
Solved

Why do I get Circular Dependancy

Making calculated columns my first one is this :

IF('AVN Rework Merge'[Flow] = "Reworked" && [Amt USD] > -50,[Quantity],0)

 

It works fine and gives me the quantity that I expected.

 

Now I want a similar one that gives met the USD amount instead of the Quantity so I made :

IF('AVN Rework Merge'[Flow] = "Reworked" && [Amt USD] > -50,[Amt USD],0)

 

But then suddenly it says I have a circular dependancy and I cannot understand why it is doing this.

 

Need Help
  • m_alireza's avatar
    m_alireza
    3 years ago

    rpinxt Instead of referencing the measure [FinAmtUSD], try referencing the column directly instead.

    so:

    Reworked USD < 50 = IF('AVN Rework Merge'[Flow] = "Reworked" && 'AVN Rework Merge'[FinAmtUSD] > -50,'AVN Rework Merge'[FinAmtUSD],0)
  • SivaMani's avatar
    SivaMani
    3 years ago

    rpinxt I agree with m_alireza. Also, any specific reason why you went with Calculated Columns instead measures?
    I rewrote the logic as a measure for you. Check if this meets your requirement

    Reworked Qty <50__ = 
VAR Result = CALCULATE(
     [Quantity],
     FILTER('AVN Rework Merge', 'AVN Rework Merge'[Flow] = "Reworked" &&  [Amt USD] > -50)
     )
RETURN
COALESCE(Result,0)

15 Replies

  • rpinxt's avatar
    rpinxt
    Solution Sage
    3 years ago

    SivaMani 

    The Amt USD measure refers to a field from the table like :

    Amt USD = SUM('AVN Rework Merge'[FinAmtUSD])
    • SivaMani's avatar
      SivaMani
      Resident Rockstar
      3 years ago

      rpinxt, As you see in the error message, there is another column 'AVN Rework Merge'[Column] - It might be referencing the same columns in its expression.  Circular Dependency is detected whenever two columns referenced each other. Check your calculated columns logic.

  • rpinxt's avatar
    rpinxt
    Solution Sage
    3 years ago

    SivaMani 

    Well yes both logics here are Calculated Columns:

     

    Calculated Column 1:

    FirstColumn = IF('AVN Rework Merge'[Flow] = "Reworked" && [Amt USD] > -50,[Quantity],0)

     

    Calculated Column 2:

    SecondColumn = IF('AVN Rework Merge'[Flow] = "Reworked" && [Amt USD] > -50,[Amt USD],0)

     

    And yes they are looking at the same columns but column1 returns the Quantity and column2 should return the USD Amount.

     

    But as I can see they are not referencing eachother....

    • SivaMani's avatar
      SivaMani
      Resident Rockstar
      3 years ago

      rpinxt, What about the 'AVN Rework Merge'[FinAmtUSD] and 'AVN Rework Merge'[Flow]? Are they direct columns from the source or calculated in Power BI?

      • rpinxt's avatar
        rpinxt
        Solution Sage
        3 years ago

        FinAmtUSD is directly from the source.

         

        Flow is also directly from the source be it that it comes from an excel file.

        The excel file was merged with the source based on a similar field in both the source and the excel file (FlowKey).

         

        So in base the 2 fields are not calculated.

    • SivaMani's avatar
      SivaMani
      Resident Rockstar
      3 years ago

      rpinxt, I couldn't reproduce the issue based on the info you provided. Can you share your pbix file with sample data (remove any sensitive data), if possible?

      • rpinxt's avatar
        rpinxt
        Solution Sage
        3 years ago

        Could try to do that, but how can one add the pbix file here?

  • m_alireza's avatar
    m_alireza
    Solution Specialist
    3 years ago

    rpinxt Instead of referencing the measure [FinAmtUSD], try referencing the column directly instead.

    so:

    Reworked USD < 50 = IF('AVN Rework Merge'[Flow] = "Reworked" && 'AVN Rework Merge'[FinAmtUSD] > -50,'AVN Rework Merge'[FinAmtUSD],0)
    • SivaMani's avatar
      SivaMani
      Resident Rockstar
      3 years ago

      rpinxt I agree with m_alireza. Also, any specific reason why you went with Calculated Columns instead measures?
      I rewrote the logic as a measure for you. Check if this meets your requirement

      Reworked Qty <50__ = 
VAR Result = CALCULATE(
     [Quantity],
     FILTER('AVN Rework Merge', 'AVN Rework Merge'[Flow] = "Reworked" &&  [Amt USD] > -50)
     )
RETURN
COALESCE(Result,0)
      • rpinxt's avatar
        rpinxt
        Solution Sage
        3 years ago

        SivaMani m_alireza I want to thank you both because both solutions seem to work! 😁

         

        Well I chose for calculated column because I merged two sources and thought to get the correct amount on every line a calculated column would be better.

        Also I tried a measure before but could not succeed 😑

         

        Must say I do not fully understand your measure.

        What does COALESCE do?

         

        And what would you guys think is best in ways of performance?

        Would the measure be better (lighter) on the data then the calculated columns?