Forum Discussion

ZalinaBaskaty's avatar
ZalinaBaskaty
Frequent Visitor
2 years ago
Solved

Shift ID lookup

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's avatar
    AlexisOlson
    2 years ago

    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]
)

     

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    2 years ago

    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]
)