How to get first value from multiple value LOOKUPVALUE function

[ddalton]

Is it possible to get just one value from a multi-value LOOKUPVALUE result? (i.e., is there another function that can handle this?)

I have a table (Table1) that contains rows with non-unique ID values. I want to create a new column in a different table that looks for an ID in Table1 and returns its respective Value2 value. However, since there are multiple results for some (if not most or even all IDs) LOOKUPVALUE throws a "multiple result" error - as expected. 

I'm hoping to find a way to effectively perform the same search but to just return the first result it finds, rather than returning a multi-value result (or rather, return a multi-value result and then select just the first value). This would be sufficient for me since the ID and Value2 are always the same pair in this particular Table.  

Can I do this using LOOKUPVALUE and some clever DAX, or is there another function I can use? 

Table1

ID	Value1	Value2
0445	1.0	AAA
0445	1.2	AAA
0872	1.2	BBB
0954	1.1	CCC
0911	1.2	DDD
0911	1.1	DDD

Currently, if I just use the LOOKUPVALUE function to search for "0445" in the ID column of Table1 and return Value2 it will return two results: [(0445, AAA), (0445, AAA)]. 

I'm looking for a way for it to take just the first result of that array/multi-value result. 

[NickolajJessen]

[ddalton]

@NickolajJessen This is looking great! 

In the FIRSTNONBLANK function where we explicitly specify 'Table'[ID] = "0445" can we instead reference a column value? i.e., 'Table'[ID] = 'Table2'[ID]

Essentially, I'm trying to merge data from multiple tables and I'm using the ID that exists in both tables to fetch Value2 from Table1 and save it in a new column in Table2. 

[ddalton · Accepted solution]

Okay, @NickolajJessen, with your help (and similar threads on this community), I have this solution:

LOOKUPALT = CALCULATE(FIRSTNONBLANK(Table1[Value2], TRUE()), FILTER(Table1, Table1[ID] = Table2[ID]))

[paulostorrer]

Thanks, only difference I used ALLEXCEPT instead of FILTER.

[charleshale]

Love this.   Thank you.   Question: can you think of any lower processor intensive ways to do this for big tables?   Maybe innerjoin somehow?

[JiriRak]

This is great, I can´t express how much the formula helped me!

[JamesWick]

The LookupValue function works best when you have only one value returned. if you have multiple values, then it will either return the result of <alternate result> if supplied, otherwise, it will return an error. Employee 31 = LOOKUPVALUE (DimEmployee [FirstName], DimEmployee [MiddleName], "R", "Not found or Multiple results")

Regards,
J Wick