Forum Discussion
Helper IGet unique intervals from a column
So, my question is: I need to get the employees' worked hours from my company's call center. The thing is, a same employee can be in multiple calls simultaneously, however the calculated hours should be unique, for example: if the employee was in 2 calls; the first one began at 9:00 am and ended at 9:30 and the second one began at 9:00 am and ended at 9:20 am, the worked hours should be 30 minutes. The current dataset that I have is built in such way that if I just sum the durations of the calls I wouldnt get the actual worked hours due to some intervals being contained in larger intervals. How could I get through that?
| Employees | Call start | Call end |
| Employee 1 | 08/08/2022 9:00 | 08/08/2022 9:40 |
| Employee 1 | 08/08/2022 9:10 | 08/08/2022 9:30 |
| Employee 2 | 08/08/2022 10:00 | 08/08/2022 10:35 |
| Employee 2 | 08/08/2022 10:10 | 08/08/2022 10:20 |
In the example above, the employee 1 actually worked 40 minutes (duration of the biggest interval) and employee 2 worked 35 minutes.
Create a table where all the intervals are expanded into minutes throughout the day. Here's what such a table should look like:
EmployeeId | CallId | Date | MinuteId
----------------------------------------
Once you've got this, it's easy to calculate unique time. You can add as many attributes to the table as you need to track all necessary details. Your DAX for calculating the number of unique minutes will then be:
[# Minutes] = distinctcount( T[MinuteId] )
How easy is that, huh? Bear in mind, please, that MinuteId should be unique across all days. If you make it unique only throughout a day, you'll have a much harder time to calculate. I would suggest that you just number all the minutes consecutively starting from the beginning of your time.
1 Reply
daXtremeSolution Sage
Create a table where all the intervals are expanded into minutes throughout the day. Here's what such a table should look like:
EmployeeId | CallId | Date | MinuteId
----------------------------------------
Once you've got this, it's easy to calculate unique time. You can add as many attributes to the table as you need to track all necessary details. Your DAX for calculating the number of unique minutes will then be:
[# Minutes] = distinctcount( T[MinuteId] )
How easy is that, huh? Bear in mind, please, that MinuteId should be unique across all days. If you make it unique only throughout a day, you'll have a much harder time to calculate. I would suggest that you just number all the minutes consecutively starting from the beginning of your time.