Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Lookup value - error multiple values was supplied

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: 

Capture 5.JPG

Much appreciate for any help! 

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
akhilduvvuru
Helper III
Helper III

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-juanli-msft
Community Support
Community Support

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.

Capture9.JPG

 

If so, please consider any other methods.

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

Capture10.JPG

 

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
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thank you @Ashish_Mathur , it works! 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
georgy
Frequent Visitor

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

image.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.