Forum Discussion

user900's avatar
user900
Helper II
4 years ago
Solved

Measure help including circular dependency errors

Hi, Hoping someone can help me create some measures. I'm new to BI and I'm getting circular dependency errors and not sure how to resolve.

 

I need to show how much time each user is spending in an application per month. A sample of my data source looks like this. The Usage time represents hours.

UserDateUsage Time

A

1/8/2022

1.25
A1/12/20221.5
A2/01/20222.5
A2/21/20224.75
B1/19/20223
B2/12/20221.25
B3/12/20221
B3/19/202221.5

I tried the following measure and received a circular dependency error:

Monthly Usage = CALCULATE(SUM('table'[Usage Time]),FILTER(ALL(table[Date]),ENDOFMONTH(table[Date])))

 

My expected outcome is:

UserDateUsage Time
AJan2.75
AFeb7.25
BJan3
BFeb1.25
BMar22.5
 
 
Once I have this figured out, I need to determine how many users meet the annual requirement of completing 20 hours by year end. 
I created this measure: 2022 Goal = "20" (no error)
 
I created a measure for the difference because we want to measure progress too.
2022 Goal Remaining = CALCULATE([2022 Goal (in hours)]-CALCULATE(SUM('table'[Usage Time]),FILTER(ALL(table[Date]),ENDOFMONTH(table[Date]))))
 
How would I determine the number of users that have completed the goal vs not complete? In my sample data, User B would show Complete while User A would not. I tried 
Status = IF('table'[2022 Goal Remaining]="20","Complete","Not Complete") but received the cirular dependency error.
 
Any help is appreciated. Thanks!
User900
 
 
 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi user900 ,

     

    Please try:

    Total Usage = CALCULATE(SUM('Table'[Usage Time]),ALLEXCEPT('Table','Table'[User],'Table'[Date].[Year],'Table'[Date].[Month]))
    Status = 
var _2022Goal = 20
return IF(CALCULATE(SUM('Table'[Usage Time]),ALLEXCEPT('Table','Table'[User]))>=_2022Goal,"Complete","Not Complete")

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    user900 , if you need data of last day of month , try like

     

    Monthly Usage = CALCULATE(SUM('table'[Usage Time]),FILTER(ALL(table[Date]),table[Date] = EOmonth(table[Date],0)))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi user900 ,

     

    Please try:

    Total Usage = CALCULATE(SUM('Table'[Usage Time]),ALLEXCEPT('Table','Table'[User],'Table'[Date].[Year],'Table'[Date].[Month]))
    Status = 
var _2022Goal = 20
return IF(CALCULATE(SUM('Table'[Usage Time]),ALLEXCEPT('Table','Table'[User]))>=_2022Goal,"Complete","Not Complete")

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.