Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I've provided basic sample data in screenshots due to privacy. For perspective, I am actually dealing with about 250 employees on multiple levels without about 400k actions per month, so the data is a lot larger but would fundamentally work the same (I think). We need to track how our workforce utlises time so that we can assist them and use it more effectively.
End Goal
We have thresholds of time spent per activity per team and I need to flag activities that exceed these thresholds. The end goal is that we can see counts and sums on team-level and employee-level for each action's duration which has breached the threshold for that team. Running data is retrospectively updated daily.
Each team has different thresholds and they need to remain dynamic so that if they need adjustment, they can just be updated in their own table, rather than within functions.
I've tried a fair bit so far, but simply am not good enough with Power BI and DAX yet. Any help would be appreciated!
Solved! Go to Solution.
@Anonymous,
Firstly, select there fields(1,2,3) of thresholds table in Query Editor, and click “Unpivot columns”.
Secondly, click “Close &Apply” to apply the changes to Report view. Then create the following columns in timeline data table.
thresholdsColumn = LOOKUPVALUE('thresholds'[Value],'thresholds'[Action],'timeline data'[Action],'thresholds'[Attribute],'timeline data'[Level])
flag = IF('timeline data'[Duration]>'timeline data'[thresholdsColumn],1,0)
Count = CALCULATE(SUM('timeline data'[flag]),ALLEXCEPT('timeline data','timeline data'[Name]))
If the above DAX don’t help, please post expected result in table format based on the above sample data.
Regards,
Lydia
@Anonymous,
Firstly, select there fields(1,2,3) of thresholds table in Query Editor, and click “Unpivot columns”.
Secondly, click “Close &Apply” to apply the changes to Report view. Then create the following columns in timeline data table.
thresholdsColumn = LOOKUPVALUE('thresholds'[Value],'thresholds'[Action],'timeline data'[Action],'thresholds'[Attribute],'timeline data'[Level])
flag = IF('timeline data'[Duration]>'timeline data'[thresholdsColumn],1,0)
Count = CALCULATE(SUM('timeline data'[flag]),ALLEXCEPT('timeline data','timeline data'[Name]))
If the above DAX don’t help, please post expected result in table format based on the above sample data.
Regards,
Lydia
Well, I finally got around to being able to work on this again and your offered solution is working well!
Thanks for the help and patience with my response and solution mark 🙂
Sorry for the delay. I have become extremely busy on another project so haven't had time to test this solution. I'll hopefully be able to try in the next few days and will report back.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |