LOOKUP VALUE BETWEEN DATES AND MULTIPLE CONDITIONS

Question

Hello!!&nbsp;This may be already posted, but can't find it or related.&nbsp;I have 2 tables, a master table like this:&nbsp;KEYBNDSECSTART_HOUREND_HOURREPS8S218/04/23 5:00 pm19/04/23 2:00 amREPS8S118/04/23 6:00 am18/04/23 5:00 pmZATR8S318/04/23 9:00 pm19/04/23 4:00 amZATR8S118/04/23 8:00 am18/04/23 7:00 pmMAKI3S218/04/23 9:00 pm19/04/23 4:00 amQWER2S318/04/23 5:30 am18/04/23 1:30 pmZATR3S218/04/23 10:00 pm19/04/23 5:00 am&nbsp;And values table:&nbsp;KEYBNDHOURACTREPS818/04/23 5:35 pm&nbsp;REPS818/04/23 6:34 am&nbsp;ZATR319/04/23 2:30 am&nbsp;&nbsp;This is only a sample but imagine I have hundreds of rows; many KEY and BND values, and start and end hour changes for any combination of them. This means that for REPS code I can have SEC with different Start and End hours. (SEC only have values from S1 to S3). I can even have 30 mins in hour schedule, as you can see in the last row.&nbsp;Master table never changes, is a master at the end, Values table shows what happened at specific time, so I need to get that SEC value.&nbsp;I need to add a column with the vale from column SEC to values table according to CODE and LINE columns, but that is between the Start and End HOURS.&nbsp;&nbsp;&nbsp;An expected result value would be:KEYBNDHOURACTSECREPS818/04/23 5:35 pm&nbsp;S2REPS818/04/23 6:34 am&nbsp;S1ZATR319/04/23 2:30 am&nbsp;S2&nbsp;As you can see, KEY = RESP and BND = 8, so at HOUR=18/04/23 5:35 pm some value was stored in ACT column (not important for this query). So for this HOUR, we go to the master table where for those KEY &amp; BND values, HOUR is between START_HOUR and END_HOUR where SEC = S2, so I bring S2.&nbsp;&nbsp;And so.&nbsp;&nbsp;&nbsp;I hope I'd clear.&nbsp;Thanks in advance.

freemanz · Answer

hi charlito&nbsp;&nbsp;it would be clearer if you could also provide your expected result column.&nbsp;The textual description is easily flawed, like what do you mean by CODE and LINE columns?

charlito · Answer

FreemanZ&nbsp;i've already edited the original post. Maybe could be more clear now

freemanz · Answer

hi&nbsp;charlito&nbsp;not sure if i fully get you, please&nbsp;try to add a calculated column in values table like:SEC = 
MAXX(
    FILTER(
        master,
        master[KEY]='values'[KEY]
            &amp;&amp;master[BND]='values'[BND]
            &amp;&amp;master[START_HOUR]&lt;='values'[HOUR]
            &amp;&amp;master[END_HOUR]&gt;='values'[HOUR]
    ),
    master[SEC]
)it worked like:

charlito · Answer

FreemanZ&nbsp;for some reason it is not working for me like you showedKEYBNDHOURACTREPS818/04/23 5:35 pm&nbsp;REPS818/04/23 6:34 am&nbsp;ZATR319/04/23 2:30 am&nbsp;&nbsp;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&nbsp;KEYBNDSECSTART_HOUREND_HOURREPS8S218/04/23 5:00 pm19/04/23 2:00 amREPS8S118/04/23 6:00 am18/04/23 5:00 pmZATR8S318/04/23 9:00 pm19/04/23 4:00 amZATR8S118/04/23 8:00 am18/04/23 7:00 pmMAKI3S218/04/23 9:00 pm19/04/23 4:00 amQWER2S318/04/23 5:30 am18/04/23 1:30 pmZATR3S218/04/23 10:00 pm19/04/23 5:00 am&nbsp;2 is outside from :&nbsp; 22 &lt;= 2 &amp; 5 &gt;= 2, clearly 22 is not less than 2.&nbsp;&nbsp;So,tThe day is not being considered.😞&nbsp;

Forum Discussion

LOOKUP VALUE BETWEEN DATES AND MULTIPLE CONDITIONS

4 Replies

Related content

Multiple conditions Index

Multiple Donut charts with conditions

Conditional Column with Multiple Conditions

IF - Multiple conditions

Conditional Column with Multiple Conditions

KEY	BND	SEC	START_HOUR	END_HOUR
REPS	8	S2	18/04/23 5:00 pm	19/04/23 2:00 am
REPS	8	S1	18/04/23 6:00 am	18/04/23 5:00 pm
ZATR	8	S3	18/04/23 9:00 pm	19/04/23 4:00 am
ZATR	8	S1	18/04/23 8:00 am	18/04/23 7:00 pm
MAKI	3	S2	18/04/23 9:00 pm	19/04/23 4:00 am
QWER	2	S3	18/04/23 5:30 am	18/04/23 1:30 pm
ZATR	3	S2	18/04/23 10:00 pm	19/04/23 5:00 am

KEY	BND	HOUR	ACT
REPS	8	18/04/23 5:35 pm
REPS	8	18/04/23 6:34 am
ZATR	3	19/04/23 2:30 am