Forum Discussion

cplmak's avatar
cplmak
New Member
4 years ago
Solved

Lookup date between two columns and return a value

Hi. I have a conundrum. I have two tables, Violations and Shift Calendar. Id like to be able to look up the RPTTIME in the Violations table against the Shift Calendar to determine which shift the violation occured. Is this possible?

 

Violations table

 

Shift Calendar

 

Result

 

Thanks

  • amitchandak's avatar
    amitchandak
    4 years ago

    cplmak , New column in DAX

     

    Shift = maxx(filter(Table2, Table1[RPTTIme] >= Table2[Start Time] && Table1[RPTTIme] < Table2[End Time]), Table2[Shift])

    Weeknum = maxx(filter(Table2, Table1[RPTTIme] >= Table2[Start Time] && Table1[RPTTIme] < Table2[End Time]), Table2[Weeknum])

     

     

     

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    cplmak , New column in DAX

     

    Shift = maxx(filter(Table2, Table1[RPTTIme] >= Table2[Start Time] && Table1[RPTTIme] < Table2[End Time]), Table2[Shift])

    Weeknum = maxx(filter(Table2, Table1[RPTTIme] >= Table2[Start Time] && Table1[RPTTIme] < Table2[End Time]), Table2[Weeknum])