Lookupvalue only first result

[Aroc]

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	=LOOKUPVALUE([Column C];[Column A];1;[Column B];[Column B])
2	77	P 2
2	77	P 2
1	88	P 1
1	99	P 4
2	44	P 5
3	22	P 7
1	88	P 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?

[malagari]

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

CALCULATE(
   FIRSTNONBLANK(ColumnC, TRUE()),
   FILTER(Table, ColumnA = ColumnB)
)

[Aroc]

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 · Accepted solution]

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

[Anonymous]

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

[TAMOREIRA]

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.

[RaulRG]

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

[Aroc]

This works perfect, thank you very much