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 UserGood. You will want to unlearn the usage of EARLIER() and want to migrate to using variables.
Helper IHad 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.