Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Aroc
Frequent Visitor

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?

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

Capture.PNG

 

Regards,

View solution in original post

7 REPLIES 7
malagari
Responsive Resident
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
Consultant at Headspring
Aroc
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?

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

Capture.PNG

 

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

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.

Capture_PBI.JPG

 

 

 

 

 

 

 

 

 

 

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

 

 

This works perfect, thank you very much

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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