Circular dependency error

[WTAS80486]

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

[amitchandak]

@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]

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