Circular Reference Error help

[MSUSPARTAN]

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"

)

[mahenkj2 · Accepted solution]

Hi @MSUSPARTAN ,

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

BoMtQtyRequiredcWhse ItemcComponentcAsphalt intermcAsphalt interm1

10	a	3abcd	10
5	a	Zabcd
20	b	2PBabcd	20
4	c	d
12	d	2PBABCs	12

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.

[MSUSPARTAN]

Thanks for the help and explanation!

[Ashish_Mathur]

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]

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]

Hi,

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