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
RvdHeijden
Post Prodigy
Post Prodigy

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)
1 ACCEPTED SOLUTION

@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],
    ", "
)

Regards
Zubair

Please try my custom visuals

View solution in original post

5 REPLIES 5
Zubair_Muhammad
Community Champion
Community Champion

@RvdHeijden

 

Try this alternative

 

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

Regards
Zubair

Please try my custom visuals

@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

@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],
    ", "
)

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad but it still doesn't work like it should.

 

Now sometimes it returns BLANK values or a value with a ; in it for example '; Zeeland'.

I just need 1 value because all adresses are in the table 'Adressen' so its impossible to return a BLANK value or 2 values 

 

Again the formula 'Lookupvalue' worked in this rapport for the last 6 months and now all of a sudden it doesn not work anymore.

Hi @RvdHeijden,

 

If that formula returns a value like '; Zeeland', it means that the column Adressen[Adres] has duplicates. Please refer to the snapshot below to check it easily. 

Error-in-a-Formula-since-last-update

If it isn't the cause, please provide a sample that can show the issue. Please mask the sensitive parts first.

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.