Forum Discussion

MSUSPARTAN's avatar
MSUSPARTAN
Frequent Visitor
4 years ago
Solved

Circular Reference Error help

I have a formula I added in a column (cAsphalt interm) and it runs fine.  When I copy paste the exact same formula to a second column (column), I get a circular reference error?  

 

the error:    <pi>A circular dependency was detected: BOMs OPS_MRP_MAS500[Column], BOMs OPS_MRP_MAS500[cAsphalt interm], BOMs OPS_MRP_MAS500[Column].</pi>

 

Very frustrating for me 😉

 

Hoping someone can help me understand why this is happening.

 

Here is the exact code:

 

cAsphalt interm =
// Filter out everything that isnt ac or a premodified base and sum everything remaining by whse item
calculate(
sum('BOMs OPS_MRP_MAS500'[BoMtQtyRequired]),
'BOMs OPS_MRP_MAS500'[cWhse Item]='BOMs OPS_MRP_MAS500'[cWhse Item] &&
left('BOMs OPS_MRP_MAS500'[cComponent],1)<>"Z" &&
left('BOMs OPS_MRP_MAS500'[cComponent],1)="3" ||
left('BOMs OPS_MRP_MAS500'[cComponent],3)="2PB"
)
 

 

Need Help
  • mahenkj2's avatar
    mahenkj2
    4 years ago

    Hi MSUSPARTAN ,

     

    I made sample data to simulate the problem and yes, the problem is there with this formula!

     

    BoMtQtyRequiredcWhse ItemcComponentcAsphalt intermcAsphalt interm1

    10a3abcd10 
    5aZabcd  
    20b2PBabcd20 
    4cd  
    12d2PBABCs12 

     

    The duplicated column show error with the formula you mentioned.

     

    Reason looks to be use of CALCULATE in calculated column and Context transition. In this case, second column cal not be calculated untill the first is calculated.

     

    This is well explained at below link:

     

    https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

     

    Marco and Russo are expert of the topic, it is best to learn from thier original text.

     

    Some key text I mention here for reference:

     

    Let us start analyzing the first scenario: context transition in calculated columns. If you do not pay attention to circular dependencies, you cannot create more than one calculated column in a table – if the formula of the column contains CALCULATE anywhere. Indeed, CALCULATE in a calculated column performs a context transition and makes that column dependent on all the columns in the table. If two such columns exist, they depend on each other. Therefore, you experience circular dependency only once you have created the second column.

The correct solution to avoid this is to restrict the list of columns that the calculated column depends on, by using ALLEXCEPT or REMOVEFILTERS and keeping only the table’s primary key. If the table has no primary key, then using CALCULATE in a calculated column is dangerous; this is because context transition is likely to produce unexpected results.

     

    Please modify the formula suitably.

     

    Hope it helps.

5 Replies

  • mahenkj2's avatar
    mahenkj2
    Solution Sage
    4 years ago

    Hi MSUSPARTAN ,

     

    I made sample data to simulate the problem and yes, the problem is there with this formula!

     

    BoMtQtyRequiredcWhse ItemcComponentcAsphalt intermcAsphalt interm1

    10a3abcd10 
    5aZabcd  
    20b2PBabcd20 
    4cd  
    12d2PBABCs12 

     

    The duplicated column show error with the formula you mentioned.

     

    Reason looks to be use of CALCULATE in calculated column and Context transition. In this case, second column cal not be calculated untill the first is calculated.

     

    This is well explained at below link:

     

    https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

     

    Marco and Russo are expert of the topic, it is best to learn from thier original text.

     

    Some key text I mention here for reference:

     

    Let us start analyzing the first scenario: context transition in calculated columns. If you do not pay attention to circular dependencies, you cannot create more than one calculated column in a table – if the formula of the column contains CALCULATE anywhere. Indeed, CALCULATE in a calculated column performs a context transition and makes that column dependent on all the columns in the table. If two such columns exist, they depend on each other. Therefore, you experience circular dependency only once you have created the second column.

The correct solution to avoid this is to restrict the list of columns that the calculated column depends on, by using ALLEXCEPT or REMOVEFILTERS and keeping only the table’s primary key. If the table has no primary key, then using CALCULATE in a calculated column is dangerous; this is because context transition is likely to produce unexpected results.

     

    Please modify the formula suitably.

     

    Hope it helps.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    This should be written as a measure (not as a calculated column formula).  Also, in your measure, why have you add this condition?  'BOMs OPS_MRP_MAS500'[cWhse Item]='BOMs OPS_MRP_MAS500'[cWhse Item].  Remove thsi conditiion.

    • MSUSPARTAN's avatar
      MSUSPARTAN
      Frequent Visitor
      4 years ago

      thanks for the reply, I appreciate the help.  I get that the code is a little wonky.  I just don't understand why it works in 1 column, but when I copy/paste the exact same code to another column it gives an error.

       

      a sample data set

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        4 years ago

        Hi,

        I cannot help you without seeing your file.  Share the download link of our PBI file and clearly show the problem there.