Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

 

Basic example of timeline dataBasic example of timeline dataAssumption on how a thresholds matrix (table) should/may lookAssumption 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
v-yuezhe-msft
Employee
Employee

@Anonymous,

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.

View solution in original post

3 REPLIES 3
v-yuezhe-msft
Employee
Employee

@Anonymous,

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.
Anonymous
Not applicable

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

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.