Counting online employees from datetime range (15minute intervals)

[LJ16923]

Dear community, 

I need to produce a visualisation detailing the amount of employees (drivers) we have online over a single day (usually yesterday) from data detailing the time a driver has logged on and the time they have logged off. They are able to also log break time (column [Type] can either be 'Break' or 'Online', I only want a driver to be counted when type is 'Online'). 

Please see example data below:

Figure 1

I want to plot a graph showing how many drivers we have online over every 15minute interval of the day. 

I am working out driver online count from the following code (measure): 

Driver Online Count = CALCULATE( COUNTROWS(DriverPeriod), DriverPeriod[Type]="Online")

The table above links to another table showing driver jobs. From this other table I have created 15minute intervals for the day which I have used to plot the following graph: 

Figure 2

This graph is incorrect - it seems to be counting the instances it which a driver is logging on as online as opposed to cumulatively counting how many drivers are online at any given moment. 

Any help on this would be greatly appreciated. 

[V-pazhen-msft · Accepted solution]

@LJ16923 

To create the interval you may used the add group feature by setting the bin size to 15 minutes, then create a count measure = COUNT([Column]). 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[sayaliredij]

You need to create a bridge table that specifies 15 Minutes Duration and Driver Id which are online at that duration. If I would be given an option I will create such a table in the source database (SQL, snowflake) . But in case you want to create in Power BI 

You can create a Calculated Table using the following Formule (please make sure you handle the dates)

Bridge =

FILTER(

CROSSJOIN(DriverData,'Time'),

[TimeTo] >= [From] &&

[TimeFrom] <= [To])

Prepare Another Time which has Duration means fromTime and ToTime

Then make relationship with Bridge table and Time table as follows

Create Measure using following Formule

Cnt = IF(ISBLANK(DISTINCTCOUNT(Bridge[DriverId])),0,DISTINCTCOUNT(Bridge[DriverId]))

Create Slicer for Type (online)

Then graph looks like following

For Your reference I used following data

Regards,

Sayali

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

[LJ16923]

Hi Sayali, 

Thanks very much for taking the time to reply. 

Unfortunately this hasn't worked and I am not sure what I am doing wrong. 

The driver count measure is 0 and the bridge table appears to have no data in it...

Code for table is as follows:

Is something incorrect here? I am thinking this is where it has gone wrong as when I try to pull a table visual from this data no data appears in visual. 

Time data is as follows:

I have related the Bridge table and Time table based on 'Hour' column. 

(I have named bridge table 'Drivers Online' )

I have gone over this several times and am confident I have followed your steps precisely - can you spot any errors here? 

[sayaliredij]

Hi

Check the date factor at this time. It seems there is something and in filter criteria, it's not matching

Thanks,

Sayali

[LJ16923]

Hi Sayali, 

You said initially if you could you would do this is in source database sql. 

Please could you explain how to achieve this. 

[Anonymous]

@LJ16923 

To have a well-defined measure you have to... well, define the construction of the measure. For instance, you have to say what it means to be online over a certain period of time. For instance, if you select a whole day, what does it mean for a driver to be classified as "online" for the day? And for an hour? Or for a morning/afternoon/night?

Without sharp definitions it's not possible to create a good measure that does something people will understand.