LOOKUP VALUE BETWEEN DATES AND MULTIPLE CONDITIONS

[charlito]

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

And values table:

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

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.

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.

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. 

 An expected result value would be:

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

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 & BND values, HOUR is between START_HOUR and END_HOUR where SEC = S2, so I bring S2. 

And so.

I hope I'd clear.

Thanks in advance.

[FreemanZ]

hi @charlito 

it would be clearer if you could also provide your expected result column.

The textual description is easily flawed, like what do you mean by CODE and LINE columns?

[charlito]

@FreemanZ 

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

[FreemanZ]

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]

@FreemanZ 

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

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

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

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

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

So,tThe day is not being considered.

😞