Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ZalinaBaskaty
New Member

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!!!! 

ZalinaBaskaty_0-1699907845821.pngZalinaBaskaty_1-1699907897245.png

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

 

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

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

 

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

ZalinaBaskaty_0-1699916912601.png

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors