Lookup value based on Max Value from another column.

[BILearn]

Hi all, 

I am trying to achieve the below result in My Table.

Established relationship between Lookup Table and My Table using Value ID.

I am trying to lookup Result  in Lookup table for Z1 and X1 with the highest Ep No (3 in this case, however this can be any number, so need the Maximum value) which is Gamma.

I tried the below code, however I am getting result as Alpha.

I have attached the PBIX here

Can you please help.

.

Codes used 

Result = calculate (

LASTNONBLANK('Lookup Table'[Result],1),

FILTER(

All('Lookup Table'),

'Lookup Table'[Value ID] = 'My Table'[Value ID]

&& 'Lookup Table'[Ep No] = 1))

and 

Result = calculate (
FIRSTNONBLANK('Lookup Table'[Result],1),
FILTER(
All('Lookup Table'),
'Lookup Table'[Value ID] = 'My Table'[Value ID]
&& 'Lookup Table'[Ep No] = 1))

[v-eqin-msft · Accepted solution]

Hi  @BILearn ,

If you final data is 

please try:

Column = 
var _value=CALCULATE(MIN('Lookup Table'[Ep No]),ALLEXCEPT('Lookup Table','Lookup Table'[Value ID]))
return LOOKUPVALUE('Lookup Table'[Result],[Ep No],_value,[Value ID],[Value ID])

Or if you data is as the original shown, please change MIN() to MAX()

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BILearn]

@v-eqin-msft Thanks a lot for your help. Apologies for the delay in responding to your solution.

[BILearn]

Hi @amitchandak 

Apologies if I were not clear in my original post.

I just tried the solution again and then realised that the code is looking only at the result column and is taking the MAX value from it.

I guess the code doesn't consider the EP No values at all.

For example, when I reversed the Ep No between Alpha and Gamma as in the image ,  its showing the same results.

Is it possible to Lookup the Result Value with the highest Ep No.

Updated Sample PBiX here

Kindly advise.

Thank you.

[amitchandak]

@BILearn ,

new column in my Table

Maxx(filter('Lookup Table', 'Lookup Table'[Value ID] = 'My Table'[Value ID]) , 'Lookup Table'[Result])

[trd5004]

Hello,

Can you use this function with the allexcept function?  I want to return values based on other criteria.

Thanks!