topic Re: Lookup value - error multiple values was supplied in Desktop

Lookup value - error multiple values was supplied

Anonymous — Thu, 15 Aug 2019 22:34:04 GMT

Hi all,

I tried to create a calculated column from the left table to lookup value from the right table but it shows this error "A table of multiple values was supplied where a single value was expected.".

This is calculated column:

lookup = LOOKUPVALUE('USA recurring'[USD revenue],'USA recurring'[Customer ID],'monthly groupby'[customer_id],'USA recurring'[Year billed],'monthly groupby'[year],'USA recurring'[month billed converted],'monthly groupby'[month])

Is there a way to bypass it?

Here are my tables:

Much appreciate for any help!

Re: Lookup value - error multiple values was supplied

georgy — Fri, 16 Aug 2019 02:53:24 GMT

Hi,

Try to use merge query instead. Go to edit queries, select your table, click merge queries (home tab) and left join with other table. Select columns that you need to match. Make sure your join kind is left outer. See attached photo. Then expand and select the column you need to join (lookup).

Cheers

Re: Lookup value - error multiple values was supplied

Ashish_Mathur — Fri, 16 Aug 2019 03:25:13 GMT

Hi,

Does this work?

CALCULATE(SUM('USA recurring'[USD revenue]),FILTER('USA recurring','USA recurring'[Customer ID]=EARLIER('monthly groupby'[customer_id])&&'USA recurring'[Year billed]=EARLIER('monthly groupby'[year])&&'USA recurring'[month billed converted]=EARLIER('monthly groupby'[month])))

Re: Lookup value - error multiple values was supplied

v-juanli-msft — Fri, 16 Aug 2019 08:32:32 GMT

Hi Anonymous

I tried your Expression, and it works well on my side.

The column is calculated in Table ‘monthly groupby’. You could try it again.

Regarding your error, it is often caused by duplicate values.

For example,

table a table b

col1 col2 col3

a a 1

b a 2

b 3

b 4

It may throw an error when we use "lookupvalue" to get value from table b into table a.

If so, please consider any other methods.

both solutions of georgy and Ashish_Mathur may work, it depends what final output you need.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Lookup value - error multiple values was supplied

Anonymous — Fri, 16 Aug 2019 18:14:28 GMT

Thank you Ashish_Mathur , it works!

Re: Lookup value - error multiple values was supplied

Ashish_Mathur — Sat, 17 Aug 2019 02:14:37 GMT

You are welcome.

Re: Lookup value - error multiple values was supplied

akhilduvvuru — Mon, 03 Apr 2023 13:28:49 GMT

Ashish_Mathur v-juanli-msft - I have a similar requirement where I have to just bring multiple values from other table using Lookup value function.

When I use LOOKUPVALUE, I'm getting this error: "A table of multiple values was supplied where a single value was expected"
When I use FIRSTNONBLANK, I getting only one value from the other table. However I need all the matched values (few cases only 1 value, few cases more than 1).

Can you please help me with the same? Thanks!

Re: Lookup value - error multiple values was supplied

Ashish_Mathur — Tue, 04 Apr 2023 01:27:52 GMT

Hi,

Both those functions will only bring over a single value. To bring over multiple values (in multiple rows), use the Query Editor to merge the two tables.