Shift ID lookup

[ZalinaBaskaty]

Hello!

I am trying to create a new column in my whse_tran table (first screenshot) with SHIFT_ID assigned to every transaction (row) based on a shift_sched table (secondscreenshot). Shift ID is determined by the START_TIME of the transaction. Could someone help me with the DAX script to accurately bring in shift IDs into transactional data?

Thank you!!!! 

[AlexisOlson · Accepted solution]

Filter shift_sched for START_TIME between SHIFT_START and SHIFT_END and take the (max) SHIFT_ID corresponding to that filtered table.

MAXX (
    FILTER (
        shift_sched,
        shift_sched[SHIFT_START] <= whse_tran[START_TIME]
            && whse_tran[START_TIME] < shift_sched[SHIFT_END]
    ),
    shift_sched[SHIFT_ID]
)

[ZalinaBaskaty]

Perfect!!! Thank you so very much, Alexis!!!!