Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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: 

Much appreciate for any help! 

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

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

7 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

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

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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.

  • georgy's avatar
    georgy
    Frequent Visitor
    7 years ago

    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

     

     

  • akhilduvvuru's avatar
    akhilduvvuru
    Helper IV
    3 years ago

    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's avatar
      Ashish_Mathur
      Super User
      3 years ago

      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.