Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Circular Dependency in calculated column

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 :(
 
 
Need Help
Tutorial Requests

4 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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" )