topic Re: Circular dependency error in DAX Commands and Tips

Circular dependency error

WTAS80486 — Wed, 08 Mar 2023 18:57:12 GMT

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

Re: Circular dependency error

amitchandak — Thu, 09 Mar 2023 02:26:48 GMT

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

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/

Re: Circular dependency error

WTAS80486 — Fri, 10 Mar 2023 14:14:16 GMT

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