cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RvdHeijden Member
Member

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

Accepted Solutions
Super User
Super User

Re: Error in a Formula since last update

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

Re: Error in a Formula since last update

@RvdHeijden

 

Try this alternative

 

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

Re: Error in a Formula since last update

@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

Super User
Super User

Re: Error in a Formula since last update

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

Re: Error in a Formula since last update

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

v-jiascu-msft Super Contributor
Super Contributor

Re: Error in a Formula since last update

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
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 339 members 3,675 guests
Please welcome our newest community members: