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,

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,

RaulRG's avatar
RaulRG
Frequent Visitor
6 years ago

 

 

 

 

 

 

 

 

 

 

Hi, All

 

I have used this approach and it is partly working for me. I would like to extract the the value of the column Z_result by filtering in Variable 1 and Variable 2 (first value of Z_Results when the values of Variable 1 and Variable 2 are equal). 

 

By using FIRSTNONBLANK function, I am not getting what I want (see Unique ID 7 and 8 in the attached picture).

 

The attached pic is just an oversimplication of a much more complex set of data with 1000s of rows

 

Thanks for your help

 

------------------------------

 

ZZ_First Occurence =
CALCULATE (
FIRSTNONBLANK(Sheet1[Z_Resul],TRUE()),
FILTER (
Sheet1,
Sheet1[Variable 1] = EARLIEr ( Sheet1[Variable 1] )
&& Sheet1[Variable 2] = EARLIEr ( Sheet1[Variable 2] ) ))