Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

LOOKUPVALUE returns error

Hi ,

 

Trying LOOKUP value but running into issues. Table 1 has distinct empid.Table two can have multiple rows.  I have sorted Table2 DESC so it picks value by latest date -  matchs and returns the score but its throwing an error - "A Table of Multiple Values was supplied'. Wondering how we can fix this.

 

 

Column  = LOOKUPVALUE(Table2[Score],Table1[EmpID],Table2[EmpID])

 

Table 1

 
EmpID
111
112
113

 

 

Table2

 

EmpIDScoreDateOutput
1111012/5/1910
111912/4/19 
111812/3/19 
112312/5/193
112412/4/19 
113412/6/194

 

Thanks

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can create a calculated column to meet your requirement:

     

    Column =
SUMX (
    TOPN (
        1,
        FILTER ( 'Table2', [EmpID] = EARLIER ( Table1[EmpID] ) ),
        [Date], DESC
    ),
    [Score]
)

     


    Best regards,

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Table 2 actually is this . It has similar values on different rows. But we need to pick the first using lookup

    EmpIDScoreDateOutput
    111112/5/191
    111112/4/19 
    111112/3/19 
    112112/5/191
    112112/4/19 
    113112/6/191
    • v-lid-msft's avatar
      v-lid-msft
      Community Support
      6 years ago

      Hi Anonymous ,

       

      We can create a calculated column to meet your requirement:

       

      Column =
SUMX (
    TOPN (
        1,
        FILTER ( 'Table2', [EmpID] = EARLIER ( Table1[EmpID] ) ),
        [Date], DESC
    ),
    [Score]
)

       


      Best regards,

       

    • v-lid-msft's avatar
      v-lid-msft
      Community Support
      6 years ago

      Hi Anonymous ,

       

      How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


      Best regards,