Forum Discussion

WTAS80486's avatar
WTAS80486
Icon for Helper IV rankHelper IV
3 years ago

Circular dependency error

Hi,

 

Can anyone help with circular dependency error

 

I have this i 1st measure in 1st table which calculates sum of worked hours  for each month for each person in the 1st table 'labor'

 

Sum of Worked Hours by month = IF(ISBLANK(MINX (labor,labor[Month End date])),0 ,CALCULATE(SUM(labor[Worked Hours]),ALLEXCEPT(labor, labor[Person ID],labor[Month End date])))
 
2nd measure which pulls total hours for each person each month from 2nd table ' hours' column total logged hours
 
Total Hours(Measure) =
VAR _Table =
    FILTER (
        hours,hours[Login ID]=MAX(_labor[Person ID]) &&
        hours[Date]=MAX(labor[Month End date])
           
    )
RETURN
    MINX ( _Table, hours[Total Logged Hours])
 
With the  above two measures i created two calculated columns in 3rd caluated table(monthlysummary) which also pulls in user id  and hierarchy from 3rd table 'user'
 
 "Sum of worked Hours ", IF(LOOKUPVALUE(user[User Status  Date], user[Login ID],labort[Person ID]) <= labor[Month End date],[Sum of worked Hours by month],0)
   
      "Total Hours", IF(LOOKUPVALUE(user[User Status Date], user[Login ID],labor[Person ID]) <= labor[Month End date],[Total Hours(Measure)],0)
 
I am using these in the final measure
 
Usage =
CALCULATE (
    (DIVIDE(SUM(monthlysummary[Sum of worked Hours]),SUM(monthlysummary[Total Hours]),0 )))
 
 
here is where i get circular dependency error

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    WTAS80486 , Using a measure in a calculated column or calculated table is not a good idea, as those can not take slicer values and are just static calculations.

    Better to have measure on top of those measures

     

    like

     

    Sum of worked Hours  = Sumx(labor,  IF(LOOKUPVALUE(user[User Status  Date], user[Login ID],labort[Person ID]) <= labor[Month End date],[Sum of worked Hours by month],0) )

     

    Sumx(labor, IF(LOOKUPVALUE(user[User Status Date], user[Login ID],labor[Person ID]) <= [Month End date],[Total Hours(Measure)],0) )

     

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

    • WTAS80486's avatar
      WTAS80486
      Icon for Helper IV rankHelper IV
      3 years ago

      amitchandak These measures on top of measures are not displaying the correct calculation for worked hours and total hours in the calculated table