cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Yojo Frequent Visitor
Frequent Visitor

Capturing rows from a timeline with multiple thresholds

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.

 

Timeline.PNGBasic example of timeline dataThresholds.PNGAssumption on how a thresholds matrix (table) should/may look

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Capturing rows from a timeline with multiple thresholds

@Yojo,

Firstly, select there fields(1,2,3) of thresholds table in Query Editor, and click “Unpivot columns”.
1.JPG

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]))
2.JPG

If the above DAX don’t help, please post expected result in table format based on the above sample data.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Moderator v-yuezhe-msft
Moderator

Re: Capturing rows from a timeline with multiple thresholds

@Yojo,

Firstly, select there fields(1,2,3) of thresholds table in Query Editor, and click “Unpivot columns”.
1.JPG

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]))
2.JPG

If the above DAX don’t help, please post expected result in table format based on the above sample data.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yojo Frequent Visitor
Frequent Visitor

Re: Capturing rows from a timeline with multiple thresholds

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.

Yojo Frequent Visitor
Frequent Visitor

Re: Capturing rows from a timeline with multiple thresholds

@v-yuezhe-msft

 

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 Smiley Happy

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 311 members 2,971 guests
Please welcome our newest community members: