To look up value in another table between two values and no unique data

[Exhibition]

I am brand new to Power BI and having a few issues to look up values between 2 columns in another table.

So, I have two tables (placed below) and I am trying to take value from TABLE1 in the column "In Serial" and according the column "Data" (date) go to TABLE2 match the date and then look up the value between two columns "Value in" and "Value out" when it found, the desired result is to add a new column in TABLE1 with the information as "text" from Column "ProgramN" in TABLE2.

TABLE1

TABLE2

The result would be something like this:

TABLE1

Thank you very much in advance for any help!! Please let me know if you would like any additional information 🙂

[theov]

Hi there,

It is well explained in this video as well:

https://www.youtube.com/watch?v=3LP5kmrJT5I

[Anonymous]

Thank you Stachu. I tried this solution. I spent 2 weeks for this. 

[Stachu · Accepted solution]

try this code as calculated column in Table1, you may need to adjust table and column names

ProgramN =
VAR __Date = 'Table1'[Data]
VAR __InSerial = 'Table1'[IN SERIAL]
VAR __RelevantRowsTable2 =
    FILTER (
        'Table2',
        'Table2'[Date] = __Date
            && 'Table2'[Value in] <= __InSerial
            && 'Table2'[Value out] >= __InSerial
    )
RETURN
    CALCULATE ( FIRSTNONBLANK ( 'Table2'[ProgramN], TRUE ), __RelevantRowsTable2 )

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

[Exhibition]

Stachu thank you very much, I spent much time trying to resolve this, now it worked, so happy, thx!! 🙂