# Lookupvalue with multiple records



## alirulez (Aug 14, 2019)

Hi all

I need some help on PowerBI

Im trying to lookup a value from another table, except that other table has multiple rows with the same value that im looking up.

This ofcourse results in an error.

Each row has a 'createdon' date, so I thought it would be best to lookup the row with the earliest 'createdon' date.

So in short, i need a lookupvalue [or a summarize formula] which will lookup the 'customerid_account.name' column within the 'opportunity' table, and return the most recent 'statuscode' (also from within the opportunity table)

I hope this is clear. Ive tried various options but I cant seem to get it.

Thanks in advance
Ali


----------



## jorismoerings (Aug 19, 2019)

Hi Try this:

try a calculated column on Opportunity Table with this:
=
VAR Mydate =
    CALCULATE (MIN ( AnotherTable[Date] ), FILTER ( CALCULATETABLE ( VALUES ( AnotherTable ) ), AnotherTable[Date] = Opportunity[CreatedOnDate]))
RETURN
    CALCULATE (MIN ( AnotherTable[Value] ), FILTER ( CALCULATETABLE ( VALUES ( AnotherTable) ), AnotherTable[Date]= mydate ))


----------

