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

Lookup value based on Max Value from another column.

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.

BILearn_0-1639568507513.png.

 

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

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi  @BILearn ,

 

If you final data is 

Eyelyn9_0-1639964729946.png

 

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

Eyelyn9_1-1639964750282.png

 

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

Eyelyn9_2-1639964804271.png

 

 

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.

 

View solution in original post

5 REPLIES 5
v-eqin-msft
Community Support
Community Support

Hi  @BILearn ,

 

If you final data is 

Eyelyn9_0-1639964729946.png

 

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

Eyelyn9_1-1639964750282.png

 

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

Eyelyn9_2-1639964804271.png

 

 

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.

 

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

BILearn
Frequent Visitor

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.

BILearn_0-1639703683367.png

BILearn_1-1639703731398.png

 

amitchandak
Super User
Super User

@BILearn ,

new column in my Table

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

Hello,

 

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

 

Thanks!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.