Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Circular dependency was detected

Hello,

 

Would someone help me please.

I am trying to get the rating of each staff per KPI based on thier current score.

 

I have my "Monthly Score" table

nameATT_%AHT_FCR_%NPS_QA_%
Staff 1100.00%785.4550.00%33.333354.67%
Staff 287.50%1529.0533.33%33.33330.00%
Staff 393.75%749.32100.00%10076.50%
Staff 498.08%628.5060.00%600.00%
Staff 5100.00%1166.59100.00%00.00%

 

I also have this "Target" table that consist of the Rating based on the score Range. It also consist of Department and Month that need to be consider also.

 

I used the below calculated column to get the ATT Rating and it seems run accurately.

 
ATT Rating_ =
IF([ATT_%]=1,5,
    CALCULATE(
        FIRSTNONBLANK(Target[Rating],1),
        FILTER(Target,Target[Deparment] = 'Monthly Score'[Department]
            && [ATT_%]>Target[ATT Min]
            && [ATT_%]<Target[ATT Max]
            && 'Monthly Score'[Month_]=Target[Month]
)))

 

Here is the result, which I think captures the correct rating.

 

My problem is when I'm going to get the other KPIs rating, using the same pattern of calculated column, I got an error.

 

AHT Rating_ =
IF ([AHT_] = 1,5,
    CALCULATE (
        FIRSTNONBLANK( Target[Rating],1),
        FILTER (Target,Target[Department] = 'Monthly Score'[Department]
                && [AHT_] > Target[AHT Min]
                && [AHT_] < Target[AHT Max]
                && 'Monthly Score'[Month_] = Target[Month]
)))
 
Here is the error:

 


 

Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    3 years ago

    Anonymous 

     

    You can't have two or more calculated columns using CALCULATE unless you have a unique ID column in the table. 

     

    Add an ALLEXCEPT to your CALCULATE filters with the UniqueID column of the table, this will stop the columns from referencing each other when they try to convert the row context into equivalent filter context. 

     

     

    ATT Rating_ =
    IF([ATT_%]=1,5,
        CALCULATE(
            FIRSTNONBLANK(Target[Rating],1),
            FILTER(Target,Target[Deparment] = 'Monthly Score'[Department]
                && [ATT_%]>Target[ATT Min]
                && [ATT_%]<Target[ATT Max]
                && 'Monthly Score'[Month_]=Target[Month]
    ),
    ALLEXCEPT( Tablethiscolumnisin[UniqueIDofthatTable] )
    ))

     

     

     

    Alternatively you could create measures instead of calculated columns. 

     

     

     

     

     

1 Reply

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    3 years ago

    Anonymous 

     

    You can't have two or more calculated columns using CALCULATE unless you have a unique ID column in the table. 

     

    Add an ALLEXCEPT to your CALCULATE filters with the UniqueID column of the table, this will stop the columns from referencing each other when they try to convert the row context into equivalent filter context. 

     

     

    ATT Rating_ =
    IF([ATT_%]=1,5,
        CALCULATE(
            FIRSTNONBLANK(Target[Rating],1),
            FILTER(Target,Target[Deparment] = 'Monthly Score'[Department]
                && [ATT_%]>Target[ATT Min]
                && [ATT_%]<Target[ATT Max]
                && 'Monthly Score'[Month_]=Target[Month]
    ),
    ALLEXCEPT( Tablethiscolumnisin[UniqueIDofthatTable] )
    ))

     

     

     

    Alternatively you could create measures instead of calculated columns.