Circular Dependency in calculated column

[Anonymous]

Dear Team,

I have these two measures, as shown below,  

1. ZF_Check1 = ([ZF_PM-7])-([ZF_PM-6]) + ([ZF_PM-6])-([ZF_PM-5]) +([ZF_PM-5])-([ZF_PM-4])

+ ([ZF_PM-4])-([ZF_PM-3]) + ([ZF_PM-3])-([ZF_PM-2]) + ([ZF_PM-2])- ([ZF_PM-1])

+ ([ZF_PM-1])-([ZF_PM]) + ([ZF_PM])-([ZF_CM])

2. 

ZF_Check2 = ABS(sum([RecValue]))+ ABS(sum([FmtInValue]))+ abs(sum([FmtOutValue]))+

abs(sum([IssValue]))+abs(sum([ZF_AdjValueN]))+abs(sum([ZF_AdjValueP]))+1

then I need two columns as shown below but the second column shows circular dependency on column 1. I really need to use column cause I will be using slicer based on these two columns.

ZF_Formula1 = IF( [ZF_Check1] > 0 , "Check", "OK")

ZF_Formula2 = if ( [ZF_Check1] > [ZF_Check1], "Check", "OK") - Shows error as shown below

  "A circular dependency was detected: FACT_IA_IIIO_XQ227[ZF_Formula2], FACT_IA_IIIO_XQ227[ZF_Formula1], FACT_IA_IIIO_XQ227[ZF_Formula2]."

 Any help would be much appreciated, as this is a crucial requirement of the project 😞

[v-jiascu-msft]

Hi @Anonymous ,

Firstly, the result of [ZF_Formula2] is always "OK", why not adding a column of Text directly?

Secondly, you call a measure in a calculated column, which introduces a filter context by filtering conversion. So there could be a very simple solution that is replacing the measure with the formula directly like below.

ZF_Formula1 =
VAR ZF_Check1 = ( [ZF_PM-7] ) - ( [ZF_PM-6] ) + ( [ZF_PM-6] ) - ( [ZF_PM-5] ) + ( [ZF_PM-5] ) - ( [ZF_PM-4] ) + ( [ZF_PM-4] ) - ( [ZF_PM-3] ) + ( [ZF_PM-3] ) - ( [ZF_PM-2] ) + ( [ZF_PM-2] ) - ( [ZF_PM-1] ) + ( [ZF_PM-1] ) - ( [ZF_PM] ) + ( [ZF_PM] ) - ( [ZF_CM] )
RETURN
    IF ( ZF_Check1 > 0, "Check", "OK" )

Best Regards,

[Anonymous]

the first fromula1  works but in my second formula, I need to use formula 1, to compare the values, is it possible?

[Anonymous]

so basically, with your suggestion, if I create this then I get the same error of circular dependency.

ZF_Formula2 =

VAR ZF_Check1 = ( [ZF_PM-7] ) - ( [ZF_PM-6] ) + ( [ZF_PM-6] ) - ( [ZF_PM-5] ) + ( [ZF_PM-5] ) - ( [ZF_PM-4] ) + ( [ZF_PM-4] ) - ( [ZF_PM-3] ) + ( [ZF_PM-3] ) - ( [ZF_PM-2] ) + ( [ZF_PM-2] ) - ( [ZF_PM-1] ) + ( [ZF_PM-1] ) - ( [ZF_PM] ) + ( [ZF_PM] ) - ( [ZF_CM] )

VAR ZF_Check2 = ABS(sum([RecValue]))+ ABS(sum([FmtInValue]))+ abs(sum([FmtOutValue]))+

abs(sum([IssValue]))+abs(sum([ZF_AdjValueN]))+abs(sum([ZF_AdjValueP]))+1

RETURN

IF ( ZF_Check1 > ZF_Check2 , "Check", "OK" )

[v-jiascu-msft]

Hi @Anonymous ,

The [ZF_Check1] is a column now. You can use it directly in the second measure [ZF_Formula2]. The [ZF_Check2] is a static value in all rows. It should work. 

Can you share the file? Please mask the sensitive parts. You only need to keep the minimum rows that can reproduce the issue.

Best Regards,