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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
tagban
Helper I
Helper I

Schedules of employees

I tried to dig a little to find some answers and maybe my brain is just fried so I wanted to ask here to see if anyone had any experience. We have a little over 40 employees with hourly schedules in various departments. Something I need to do is coorellate that with some other data. The other data I have can go on a line chart with the obvious data point being 'start of the hour' for each event. So I have that already setup but I was asked to overlay essentially a count by department in each of those hours to show how many people WERE working in each department when the event happened. My event data is clean and has been used as it is for months hourly, but they want to see the schedule data now overlaid on it, by day of week (Because each day is different for some people but not for others. So I have it by hour and day of week in events, but can't figure out (likely tired), how to get this data into a place I can do that. I was debating making a chart of every hour of every day of every day of the week so that I can coorelate the data, knowing that my source will change soon I based the chart on the new source format so it should be pretty much a drop in replacement.

Here's an example of scheduling data:

AgentRoleMonday StartMonday EndTuesday StartTuesday EndWednesday StartWednesday EndThursday StartThursday EndFriday StartFriday End
Adamcc1899-12-30 09:00:001899-12-30 20:00:001899-12-30 09:00:001899-12-30 20:00:001899-12-30 09:00:001899-12-30 20:00:001899-12-30 09:00:001899-12-30 20:00:00  
Georgecc1899-12-30 09:00:001899-12-30 20:00:001899-12-30 09:00:001899-12-30 20:00:001899-12-30 09:00:001899-12-30 20:00:001899-12-30 09:00:001899-12-30 20:00:00  
Fredcc1899-12-30 10:00:001899-12-30 21:00:001899-12-30 10:00:001899-12-30 21:00:001899-12-30 10:00:001899-12-30 21:00:001899-12-30 10:00:001899-12-30 21:00:00  
Martha S.cc  1899-12-30 08:00:001899-12-30 19:00:001899-12-30 08:00:001899-12-30 19:00:001899-12-30 08:00:001899-12-30 19:00:001899-12-30 08:00:001899-12-30 19:00:00
Martha Y.cc  1899-12-30 10:00:001899-12-30 21:00:001899-12-30 10:00:001899-12-30 21:00:001899-12-30 10:00:001899-12-30 21:00:001899-12-30 10:00:001899-12-30 21:00:00
Melissacc  1899-12-30 09:00:001899-12-30 20:00:001899-12-30 09:00:001899-12-30 20:00:001899-12-30 09:00:001899-12-30 20:00:001899-12-30 09:00:001899-12-30 20:00:00
Jacobcc1899-12-30 08:00:001899-12-30 17:00:001899-12-30 08:00:001899-12-30 17:00:001899-12-30 08:00:001899-12-30 17:00:001899-12-30 08:00:001899-12-30 17:00:001899-12-30 08:00:001899-12-30 17:00:00
Sven W.cc1899-12-30 08:00:001899-12-30 19:00:001899-12-30 08:00:001899-12-30 19:00:001899-12-30 08:00:001899-12-30 19:00:001899-12-30 08:00:001899-12-30 19:00:00  
Johncc1899-12-30 08:00:001899-12-30 19:00:001899-12-30 08:00:001899-12-30 19:00:001899-12-30 08:00:001899-12-30 19:00:001899-12-30 08:00:001899-12-30 19:00:00  
Jessicacc1899-12-30 08:00:001899-12-30 17:00:001899-12-30 08:00:001899-12-30 17:00:001899-12-30 08:00:001899-12-30 17:00:001899-12-30 08:00:001899-12-30 17:00:001899-12-30 08:00:001899-12-30 17:00:00
Jordan Wcc1899-12-30 08:00:001899-12-30 17:00:001899-12-30 08:00:001899-12-30 17:00:001899-12-30 08:00:001899-12-30 17:00:001899-12-30 08:00:001899-12-30 17:00:001899-12-30 08:00:001899-12-30 17:00:00
Jordan Hcc1899-12-30 10:00:001899-12-30 21:00:001899-12-30 08:00:001899-12-30 19:00:001899-12-30 08:00:001899-12-30 19:00:001899-12-30 08:00:001899-12-30 19:00:00  
Meganme1899-12-30 11:00:001899-12-30 20:00:001899-12-30 09:00:001899-12-30 18:00:001899-12-30 09:00:001899-12-30 18:00:001899-12-30 09:00:001899-12-30 18:00:001899-12-30 11:00:001899-12-30 20:00:00
Marcel Wme1899-12-30 12:00:001899-12-30 21:00:001899-12-30 12:00:001899-12-30 21:00:001899-12-30 12:00:001899-12-30 21:00:001899-12-30 12:00:001899-12-30 21:00:001899-12-30 12:00:001899-12-30 21:00:00
Kellyme1899-12-30 11:00:001899-12-30 20:00:001899-12-30 11:00:001899-12-30 20:00:001899-12-30 11:00:001899-12-30 20:00:001899-12-30 11:00:001899-12-30 20:00:001899-12-30 09:00:001899-12-30 18:00:00
Kristenme  1899-12-30 08:00:001899-12-30 19:00:001899-12-30 08:00:001899-12-30 19:00:001899-12-30 08:00:001899-12-30 19:00:001899-12-30 08:00:001899-12-30 19:00:00
Kirstenme1899-12-30 10:00:001899-12-30 21:00:001899-12-30 10:00:001899-12-30 21:00:001899-12-30 10:00:001899-12-30 21:00:001899-12-30 10:00:001899-12-30 21:00:00  
Suecc  1899-12-30 09:00:001899-12-30 20:00:001899-12-30 10:00:001899-12-30 21:00:001899-12-30 09:00:001899-12-30 20:00:001899-12-30 09:00:001899-12-30 20:00:00
Sidneycc    1899-12-30 08:00:001899-12-30 21:00:001899-12-30 08:00:001899-12-30 21:00:001899-12-30 08:00:001899-12-30 21:00:00
Donatellocc1899-12-30 08:00:001899-12-30 17:00:001899-12-30 08:00:001899-12-30 17:00:001899-12-30 08:00:001899-12-30 17:00:001899-12-30 08:00:001899-12-30 17:00:001899-12-30 08:00:001899-12-30 17:00:00
            

As you can see it converts the time format to datetime but realistically I only see hh:mm:ss (We do all times in EST to avoid confusion).

 

Is the path I should follow starting a large scale by day by hour style chart? 

1 ACCEPTED SOLUTION
v-yifanw-msft
Community Support
Community Support

Hi @tagban ,
Depending on the information you provided, you can refer to the following steps:

1.Unpivot Columns.

vyifanwmsft_0-1709019112601.png

2.Add two columns.

start time = 
CALCULATE (
    MIN ( 'Table'[Value] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Agent] = EARLIER ( 'Table'[Agent] ) )
)
end time = 
CALCULATE (
    MAX ( 'Table'[Value] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Agent] = EARLIER ( 'Table'[Agent] ) )
)

3. Add new measure.

total = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Agent] ),
    FILTER (
        'Table',
        SELECTEDVALUE ( 'Table (2)'[Column1] ) >= 'Table'[start time]
            && SELECTEDVALUE ( 'Table (2)'[Column1] ) <= 'Table'[end time]
    )
)

Final output:

vyifanwmsft_1-1709019430136.png

vyifanwmsft_2-1709019447556.png

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

 

Best Regards,

Ada Wang

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

4 REPLIES 4
v-yifanw-msft
Community Support
Community Support

Hi @tagban ,
Depending on the information you provided, you can refer to the following steps:

1.Unpivot Columns.

vyifanwmsft_0-1709019112601.png

2.Add two columns.

start time = 
CALCULATE (
    MIN ( 'Table'[Value] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Agent] = EARLIER ( 'Table'[Agent] ) )
)
end time = 
CALCULATE (
    MAX ( 'Table'[Value] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Agent] = EARLIER ( 'Table'[Agent] ) )
)

3. Add new measure.

total = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Agent] ),
    FILTER (
        'Table',
        SELECTEDVALUE ( 'Table (2)'[Column1] ) >= 'Table'[start time]
            && SELECTEDVALUE ( 'Table (2)'[Column1] ) <= 'Table'[end time]
    )
)

Final output:

vyifanwmsft_1-1709019430136.png

vyifanwmsft_2-1709019447556.png

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

 

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Okay so this worked for the first part of my question. But I guess I need to make another calculated column to show the time selected to see if it actually falls within the timeframe selected. And then the same table of time with each hour linked to another report that shows it all. Otherwise i'm back to my create a seperate row for every hour of every day the people are working and link the two on "Value". Right?

I have to admit I did it wrong the first time i unpivoted selected rather than other and spent almost an hour trying to figure out why I'd do that.. but now it makes perfect sense.

Any advice on relationshipping this to another table with the names in it? Just wanting to show How many events each person has per hour and what their schedule was. 

tagban
Helper I
Helper I

My thoughts are so far to take both events table and show Day of week and Start of hour, then merge those two together, And at the same time, show a literal chart of each employee by day So like
Monday 11:00:00 AM, Monday 12:00:00 PM, etc with a 1 or 0 in it whether they work or not, I was just curious if there was a better method before I go down that rabbit hole. 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.