Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
edge9999
Frequent Visitor

Displaying the Count of Employee records when Sum of Hours is greater than 24.

‎01-31-2022 08:11 PM

We are using Power BI to report progress on employee timesheets which are fed data from an outside field ticketing system.   We look at various components of this data, but one of the things we are trying to do is identify employees who have move than 24 hours in a day and simply report the total number of distinct employees on a card.   The main data set we are using looks like this

 

EMPLOYEEIDFIRST_NAMELAST_NAMEDATEHOURSDESCRIPTIONSTATUSJOBACTIVITYBUSINESS_LINEBASETICKETCUSTOMERCOMMENTWORKED_STATESTARTTIMEENDTIMEEMP_DEFAULT_BASEEMP_PAY_PERIOD_PROFILESOURCE
2JimboJones1/29/20229TimeEntryApprovedGLE-22-PM-0052Job TimeServicesWest TexasGLE-22-PM-0052-016-077Customer 1rig up crewNM--West TexasBi-Weekly HourlyFieldTicket
2JimboJones1/29/20222TimeEntryApprovedGLE-22-PM-0052Drive TimeServicesWest TexasGLE-22-PM-0052-016-077Customer 1rig up crewNM--West TexasBi-Weekly HourlyFieldTicket
2JimboJones1/29/20226TimeEntryApprovedGLE-22-PM-0070Job TimeServicesWest TexasGLE-22-PM-0070-018-067Customer 2FieldTicket TimeTX--West TexasBi-Weekly HourlyFieldTicket
2JimboJones1/29/20224TimeEntryApprovedGLE-22-PM-0070Drive TimeServicesWest TexasGLE-22-PM-0070-018-067Customer 2FieldTicket TimeTX--West TexasBi-Weekly HourlyFieldTicket
2JimboJones1/29/20225TimeEntryApprovedGLE-22-PM-0073Drive TimeServicesWest TexasGLE-22-PM-0073-016-078Customer 1DrilloutNM--West TexasBi-Weekly HourlyFieldTicket
2JimboJones1/29/20227TimeEntryApprovedGLE-22-PM-0073Job TimeServicesWest TexasGLE-22-PM-0073-016-078Customer 1DrilloutNM--West TexasBi-Weekly HourlyFieldTicket
2JimboJones1/28/20223TimeEntryApprovedGLE-22-PM-0068Drive TimeServicesWest TexasGLE-22-PM-0068-019-096Customer 3FieldTicket TimeTX--West TexasBi-Weekly HourlyFieldTicket
2JimboJones1/28/20225TimeEntryApprovedGLE-22-PM-0068Job TimeServicesWest TexasGLE-22-PM-0068-019-096Customer 3FieldTicket TimeTX--West TexasBi-Weekly HourlyFieldTicket
2JimboJones1/27/202210TimeEntryApprovedGLE-22-PM-0068Job TimeServicesWest TexasGLE-22-PM-0068-019-095Customer 3FieldTicket TimeTX--West TexasBi-Weekly HourlyFieldTicket
2JimboJones1/26/20225TimeEntryApprovedGLE-22-PM-0052Drive TimeServicesWest TexasGLE-22-PM-0052-000-372Customer 1FieldTicket TimeNM--West TexasBi-Weekly HourlyFieldTicket
2JimboJones1/26/202216TimeEntryApprovedGLE-22-PM-0052Job TimeServicesWest TexasGLE-22-PM-0052-000-372Customer 1FieldTicket TimeNM--West TexasBi-Weekly HourlyFieldTicket
2JimboJones1/18/20222TimeEntryApprovedGLE-22-PM-0043Drive TimeServicesWest TexasGLE-22-PM-0043-000-346Customer 4FieldTicket TimeTX--West TexasBi-Weekly HourlyFieldTicket
2JimboJones1/18/20229TimeEntryApprovedGLE-22-PM-0043Job TimeServicesWest TexasGLE-22-PM-0043-000-346Customer 4FieldTicket TimeTX--West TexasBi-Weekly HourlyFieldTicket
2JimboJones1/18/20222TimeEntryApprovedGLE-00-PM-TRAIDrive TimeServicesWest Texas  Travel From LocTX07:00p09:00pWest TexasBi-Weekly Hourly 
2JimboJones1/17/20225TimeEntryApprovedICE-99-99-9999Shop TimeServicesWest Texas  loc to fix pumpsTX02:00p07:00pWest TexasBi-Weekly Hourly 
2JimboJones1/17/20222TimeEntryApprovedGLE-00-PM-SHOPShop TimeServicesWest Texas  Work on EquipTX12:00p02:00pWest TexasBi-Weekly Hourly 
2JimboJones1/16/20222TimeEntryApprovedGLE-22-PM-0036Drive TimeServicesWest TexasGLE-22-PM-0036-044-023Customer 5FieldTicket TimeTX--West TexasBi-Weekly HourlyFieldTicket
2

Jimbo

Jones1/16/202212TimeEntryApprovedGLE-22-PM-0036Job TimeServicesWest TexasGLE-22-PM-0036-044-023Customer 5FieldTicket TimeTX--West TexasBi-Weekly HourlyFieldTicket

 

So based on the above data, we are extracting the data from the 01/29/2022 as the sum of these records are greater than 24 hours.   That table displays the employee, with the total hours by day.   What we are hoping to do is to have a card on our summary dashboard that counts the number of emloyees in this situation. ie in this case, the card would show a value of 1.   The only way i have figured to do this so far is to group the data employee and Date in another dataset and then use a count of the employee measure to display the value.   It feels like there should be a way to do this without duplicating the data in another dataset.   Any suggestions would be greatly appreciated.

 

Thanks

 

Message 1 of 4
638 Views
0
1 ACCEPTED SOLUTION
edge9999
Frequent Visitor
In response to tackytechtom

‎02-04-2022 07:03 AM

Thanks a bunch.. i'll give it a try.

View solution in original post

Message 3 of 4
595 Views
0
3 REPLIES 3
Anonymous
Not applicable

‎02-08-2022 05:22 PM

Hi @edge9999 ,

 

Any updates?

 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

 

 

Best Regards,
Eyelyn Qin

Message 4 of 4
553 Views
tackytechtom
Super User
Super User

‎02-01-2022 11:49 AM

Hi @edge9999 ,

 

If I understood correctly, you would like to count all distinct [EMPLOYEEID] that have more than 24 [HOURS] per [DATE].

 

I used your data and added a couple of more dummy rows (first two rows and last row) so that the table looks like this:

tomfox_0-1643744512295.png

 

With this example data, we have 3 occasions where an employee has more than 24 hours:
- Employee 2 on 1/15/2022 with 27 hours

- Employee 2 on 1/29/2022 with 33 hours

- Employee 3 on 1/18/2022 with 32 hours

 

So, in total we have two different employees, which should be displayed on that card visual, which you can see here for tomsmeasure3:

tomfox_1-1643744763142.png

 

This is the DAX for that measure:

tomsmeasure3 = 
    IF(
        CALCULATE ( 
            SUM ('Table'[HOURS] ),
            ALLEXCEPT('Table','Table'[DATE],'Table'[EMPLOYEEID])
        ) > 24, DISTINCTCOUNT('Table'[EMPLOYEEID]), BLANK() 
    )

 

The other two measures are just dummy measures I used to get to the solution (1: sum up hours, 2: get max(ID) for hours > 24)

 

Hope this helps!

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

 

 



Did I answer your question❓➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Message 2 of 4
626 Views
0
edge9999
Frequent Visitor
In response to tackytechtom

‎02-04-2022 07:03 AM

Thanks a bunch.. i'll give it a try.

Message 3 of 4
596 Views
0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

View All