Lookup value - error multiple values was supplied

[Anonymous]

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! 

[akhilduvvuru]

@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!

[Ashish_Mathur]

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.

[v-juanli-msft]

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.

[Ashish_Mathur · Accepted solution]

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

[Anonymous]

Thank you @Ashish_Mathur , it works! 

[Ashish_Mathur]

You are welcome.

[georgy]

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