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,

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

  • 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] ) ))

     

     
  • TAMOREIRA's avatar
    TAMOREIRA
    Frequent Visitor
    6 years ago

    I am working on a patient flow projected and was having such a hard time looking up the earliest date of a consult based on patient number and visit code (consult vs. treatment vs. simulation). This solution worked for me! Thank you so much.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi ,

     

    In the same dax I want to add a condition to compare the column value, how to do that,

    this is my formula

     

    CallTypeMultipleValues = CALCULATE (
    FIRSTNONBLANK ( 'ProdConsolidatedProdReport-Ganesh'[Call Type], TRUE () ),
    FILTER (
    'ProdConsolidatedProdReport',
    'ProdConsolidatedProdReport'[Call Type] = "Callable"
    && 'InflowConsolidatedFlatFile'[EncounterNumberDuplicate] = EARLIER ( 'InflowConsolidatedFlatFile'[EncounterNumberDuplicate]
    )
    ))
     
    condtion to add s ProdConsolidatedProdReport'[Client] = 'InflowConsolidatedFlatFile'[client]
    whn I add the condition it throws an error