topic To look up value in another table between two values and no unique data in DAX Commands and Tips

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

Exhibition — Sun, 28 Jul 2019 02:56:52 GMT

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.

The result would be something like this:

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

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

Stachu — Sun, 28 Jul 2019 03:52:41 GMT

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!

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

Exhibition — Sun, 28 Jul 2019 07:47:12 GMT

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

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

Anonymous — Mon, 09 May 2022 10:26:48 GMT

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

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

theov — Mon, 24 Apr 2023 07:17:32 GMT

Hi there,

It is well explained in this video as well:

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