Find corresponding worker based on shift begining and end datetimes

[igonzalezb]

I have table A of events:

Event ID	Station ID	Timestamp (datatime)
10	A
20	B
30	A

And I have table B of workers and their shifts:

Station ID	Worker ID	Shift start (datetime)	Shift end (datetime)
A	1
A	2
B	1
A	1

I would like to fill a column on table A with the corresponding Worker ID, so I can calculate things like number of events per worker, etc.

Note datetime granularity is needed. Shifts may or may not start one day and end on the next one. There is no overlap of shifts. Some intervals of time may have no worker assigned. There is no more than one worker assigned at any given time-station combination. The same worker may be assigned to different stations on different datetimes.

How would one go about modeling this relationship in an elegant manner?

[lbendlin]

You may want to provide more details. What if worker shifts overlap  and the event at the station happened during that time?

[lbendlin · Accepted solution]

I would keep the tables disconnected, or - if you must - create a Stations dimensional table.

Regardless, the calculated column in the Events table would just reference the  entire workers_shifts table with the appropriate filters

Meta Code:

Emp = 
var s = selectedvalue([Station ID])

var d = selectedvalue([Timestamp])

Return calculate(max(workers[Worker ID]),workers[Station ID]=s,workers[Shift start]<=d,workers[Shift end]>=d)

[igonzalezb]

That works! Had to make some modifications though. Code ended up being this:

Assigned Worker = 
   CALCULATE(
      MAX(workers[Worker ID]),
      workers[Station ID] = EARLIER([Station ID]),
      workers[Shift start]<=EARLIER([Timestamp]),
      workers[Shift end] >= EARLIER([Timestamp])
   )

Thank you, @lbendlin 

[lbendlin]

Good.  You will want to unlearn the usage of EARLIER() and want to migrate to using variables.

[igonzalezb]

Had to use earlier because with selectedvalue the formula did not work. It returned blanks everywhere. But yes, instead of earlier I'm using variables like this:

Assigned Worker = 
   VAR s = [Station ID]
   VAR d = [Timestamp]
   RETURN CALCULATE(
      MAX(workers[Worker ID]),
      workers[Station ID] = s,
      workers[Shift start]<=d,
      workers[Shift end] >= d
   )

Any reason why EARLIER is not recomended even when it does the trick?

[lbendlin]

EARLIER and EARLIEST only go back one context or to the outermost context. Variables allow you to freeze the values at any context level. Kind of ironic - these are not really variables, rather constants.

[igonzalezb]

@lbendlin Hello. As stated in the post, there is no overlap. Let's assume there are no edge cases.