Forum Discussion

Aroc's avatar
Aroc
Frequent Visitor
8 years ago
Solved

Lookupvalue only first result

Hello   I've got a table that looks like this:   Column A Column B Column C Calculated Column 3 99 P 18 =LOOKUPVALUE([Column C];[Column A];1;[Column B];"99") 4 88 P 144 =LOOKUPV...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    Aroc

     

    You should use EARLIER() in your filter:

     

    Column =
CALCULATE (
    FIRSTNONBLANK ( Table4[Column C], TRUE () ),
    FILTER (
        Table4,
        Table4[Column A] = 1
            && Table4[Column B] = EARLIER ( Table4[Column B] )
    )
)

     

    Regards,

malagari's avatar
malagari
Continued Contributor
8 years ago

You can try the FIRSTNONBLANK function instead of LOOKUPVALUE.  This would look like:

 

CALCULATE(
   FIRSTNONBLANK(ColumnC, TRUE()),
   FILTER(Table, ColumnA = ColumnB)
)
  • Aroc's avatar
    Aroc
    Frequent Visitor
    8 years ago

    Hello

     

    I tried what u said and used

    =CALCULATE(
   FIRSTNONBLANK('Table'[Column C]; TRUE());
   FILTER('Table';'Table'[Column A]=1);
   FILTER('Table';'Table'[Column B]='Table'[Column B])
)

    It works half. It ignores kinda the second Filter, so the output currently is "P 1" for every line. If i replace the second 'Table'[Column B] with the actual Value of the Row (e.g. 99 for the first row, 88 for the second one...) it shows the correct value. 

     

    Any other Ideas?

    • v-sihou-msft's avatar
      v-sihou-msft
      Microsoft Employee
      8 years ago

      Aroc

       

      You should use EARLIER() in your filter:

       

      Column =
CALCULATE (
    FIRSTNONBLANK ( Table4[Column C], TRUE () ),
    FILTER (
        Table4,
        Table4[Column A] = 1
            && Table4[Column B] = EARLIER ( Table4[Column B] )
    )
)

       

      Regards,

      • Aroc's avatar
        Aroc
        Frequent Visitor
        8 years ago

        This works perfect, thank you very much