Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Why this LOOKUPVALUE does not work?

Hello

Can you tell me please why this LOOKUPVALUE in a calculated column in Table1 does not work?

 

LOOKUPVALUE('Table2'[Column3],'Table2'[Column1],Table1[Column1])

Error: A table of multiple values was supplied where a single value was expected.

 

Thanks

Need Help

5 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous 

    the reason - you have more the one coincidence

    you need to define business rule what exactly row whould be chosen for these cases

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi, do you mean there are duplicates in these columns? And I need to specify which one to consider? How can I specify that?

      • az38's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Anonymous 

        yes

        you have more than ine result resulted by your condition. you should define what result to choose. MAX() or MIN() or AVERAGE or FIRSTNONBLANK. there is a lot of possible solutions depending on your logic. you can create a column like

        CALCULATE(FIRSTNONBLANK('Table2'[Column3], 1), FILTER(ALL(Table2), 'Table2'[Column1] = EARLIER(Table1[Column1]) ) )

         

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , try like new column

    new colum = maxx(filter('Table2','Table2'[Column1],Table1[Column1]),'Table2'[Column3])

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi,

     

    It is because there are more than one same value in 'Table'[Column 1].

    Please check it.

     

    Best Regards,

    Giotto