cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 =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?

1 ACCEPTED SOLUTION
Employee

@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 7
Responsive Resident

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

```CALCULATE(
FIRSTNONBLANK(ColumnC, TRUE()),
FILTER(Table, ColumnA = ColumnB)
)```
Dan Malagari
Frequent Visitor

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?

Employee

@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
Not applicable

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
Frequent Visitor

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.

Frequent Visitor

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

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

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

Frequent Visitor

This works perfect, thank you very much

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors