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 AColumn BColumn CCalculated Column
399P 18=LOOKUPVALUE([Column C];[Column A];1;[Column B];"99")
488P 144=LOOKUPVALUE([Column C];[Column A];1;[Column B];[Column B])
277P 2 
277P 2 
188P 1 
199P 4 
244P 5 
322P 7 
188P 99 

 

My goal is that the LOOKUPVALUE finds the row where in [Column A]=1 and [Column B] is the same value as on its own row, and then gives [Column C] as output.

 

So the first row would give "P 4" as output. This works as long there arent 2 possible entries in the table. In this case, i just want the first one. The Second row would give "P 1" then.

 

Can someone help?

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,

7 Replies

  • 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,