Forum Discussion
Find corresponding worker based on shift begining and end datetimes
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)
You may want to provide more details. What if worker shifts overlap and the event at the station happened during that time?
lbendlin Hello. As stated in the post, there is no overlap. Let's assume there are no edge cases.
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)
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
Good. You will want to unlearn the usage of EARLIER() and want to migrate to using variables.
