Forum Discussion

ddalton's avatar
ddalton
Resolver I
4 years ago
Solved

How to get first value from multiple value LOOKUPVALUE function

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 v...
Need Help
  • ddalton's avatar
    ddalton
    4 years ago

    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]))
ddalton's avatar
ddalton
Resolver I
4 years ago

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's avatar
ddalton
Resolver I
4 years ago

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]))
  • JiriRak's avatar
    JiriRak
    Helper I
    3 years ago

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

  • charleshale's avatar
    charleshale
    Continued Contributor
    2 years ago

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

  • paulostorrer's avatar
    paulostorrer
    Regular Visitor
    2 years ago

    Thanks, only difference I used ALLEXCEPT instead of FILTER.