Forum Discussion
Helper IFind 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)
Super UserYou may want to provide more details. What if worker shifts overlap and the event at the station happened during that time?
Super UserI 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)
igonzalezbThat 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?