Forum Discussion

RvdHeijden's avatar
RvdHeijden
Icon for Post Prodigy rankPost Prodigy
7 years ago
Solved

Error in a Formula since last update

Hello,

 

I have a formula that always worked but (i think) since the last update of powerbi returns an error

 

It's basically a rather simple formula 

Plaats = LOOKUPVALUE(Adressen[plaats];Adressen[Adres];EFOS[Adres])
 
I have 2 different tables (Adressen and EFOS) that have a calculated column in it to combi certain fields
for example 
Adres = EFOS[postcode]&", "& EFOS[huisnr]&", "&EFOS[toevoeging]&", "&EFOS[kamer]
Adres = Adressen[postcode]&", "& Adressen[huisnr]&", "&Adressen[toevoeging]&", "&Adressen[kamer]
 
Which basically makes an unique field out of the adres and the same formula is in the table 'Adressen' and in 'EFOS' and with the lookup formula it adds the City in a third table but now the error says
"There was given a table with multiple values there where a single value was expected" roughly translated. 
 
Any ideas on how to correct that ?
There are BLANK values in the colum Adressen[Plaats]  so maybe that is the issue now ? (even though it never was before)
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    RvdHeijden

     

    My apologies

     

    Actually i wanted to write this formula.

    Lookupvalue returns error when 2 or more different values get a match

    This is just an alternate way of doing lookup

     

    Plaats =
CONCATENATEX (
    CALCULATETABLE (
        VALUES ( Adressen[plaats] ),
        FILTER ( Adressen, [Adres] = EFOS[Adres] )
    ),
    Adressen[plaats],
    ", "
)

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    RvdHeijden

     

    Try this alternative

     

    Plaats =
CONCATENATEX (
    FILTER ( Adressen, [Adres] = EFOS[Adres] ),
    Adressen[plaats],
    ", "
)
    • RvdHeijden's avatar
      RvdHeijden
      Icon for Post Prodigy rankPost Prodigy
      7 years ago

      Zubair_Muhammad

      Thank you for responding but that new formula doesnt return the correct value because sometimes it returns 1, 2 or even 3 values

      I believe it should be a lookupvalue because i need the value from another table to be placed in another table

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        7 years ago

        RvdHeijden

         

        My apologies

         

        Actually i wanted to write this formula.

        Lookupvalue returns error when 2 or more different values get a match

        This is just an alternate way of doing lookup

         

        Plaats =
CONCATENATEX (
    CALCULATETABLE (
        VALUES ( Adressen[plaats] ),
        FILTER ( Adressen, [Adres] = EFOS[Adres] )
    ),
    Adressen[plaats],
    ", "
)