Forum Discussion

BILearn's avatar
BILearn
Frequent Visitor
4 years ago
Solved

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.

.

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    BILearn ,

    new column in my Table

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

    • trd5004's avatar
      trd5004
      Regular Visitor
      3 years ago

      Hello,

       

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

       

      Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      BILearn
      Frequent Visitor
      4 years ago

      Anonymous Thanks a lot for your help. Apologies for the delay in responding to your solution.

  • BILearn's avatar
    BILearn
    Frequent Visitor
    4 years ago

    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.