Forum Discussion

charlito's avatar
charlito
New Member
3 years ago

LOOKUP VALUE BETWEEN DATES AND MULTIPLE CONDITIONS

Hello!!   This may be already posted, but can't find it or related.   I have 2 tables, a master table like this:   KEY BND SEC START_HOUR END_HOUR REPS 8 S2 18/04/23 5:00 pm 19/04...
different times
LOOKUPVALUE
multiple conditions
charlito's avatar
charlito
New Member
3 years ago

FreemanZ 

i've already edited the original post. Maybe could be more clear now

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

hi charlito 

not sure if i fully get you, please try to add a calculated column in values table like:

SEC = 
MAXX(
    FILTER(
        master,
        master[KEY]='values'[KEY]
            &&master[BND]='values'[BND]
            &&master[START_HOUR]<='values'[HOUR]
            &&master[END_HOUR]>='values'[HOUR]
    ),
    master[SEC]
)

it worked like:

  • charlito's avatar
    charlito
    New Member
    3 years ago

    FreemanZ 

    for some reason it is not working for me like you showed

    KEYBNDHOURACT
    REPS818/04/23 5:35 pm 
    REPS818/04/23 6:34 am 
    ZATR319/04/23 2:30 am 

     

    It is not considering the day (18/04/23) as part of the formula, it is only taking the hour.
    So for me it is not retrieving a value for ZATR row.

    So when day changes in master from 18 to 19, it is not considering that 2:30 am is in that range, because ot only takes the hour, so

     

    KEYBNDSECSTART_HOUREND_HOUR
    REPS8S218/04/23 5:00 pm19/04/23 2:00 am
    REPS8S118/04/23 6:00 am18/04/23 5:00 pm
    ZATR8S318/04/23 9:00 pm19/04/23 4:00 am
    ZATR8S118/04/23 8:00 am18/04/23 7:00 pm
    MAKI3S218/04/23 9:00 pm19/04/23 4:00 am
    QWER2S318/04/23 5:30 am18/04/23 1:30 pm
    ZATR3S218/04/23 10:00 pm19/04/23 5:00 am

     

    2 is outside from :  22 <= 2 & 5 >= 2, clearly 22 is not less than 2. 

     

    So,tThe day is not being considered.

    😞