LOOKUPVALUE returns error

[Anonymous]

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

EmpID	Score	Date	Output
111	10	12/5/19	10
111	9	12/4/19
111	8	12/3/19
112	3	12/5/19	3
112	4	12/4/19
113	4	12/6/19	4

Thanks

[Anonymous]

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

EmpID	Score	Date	Output
111	1	12/5/19	1
111	1	12/4/19
111	1	12/3/19
112	1	12/5/19	1
112	1	12/4/19
113	1	12/6/19	1

[v-lid-msft]

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,

[v-lid-msft · Accepted solution]

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,