Need help on lookupvalue

[gauravnarchal]

Hello

I am getting an error "A table of multiple values was supplied where a single value was expected." when using lookupvalue. How can I find citycode from table 1 and match it with citycode in table 2 and return country from table 2?

Some of the cityccode in table 1 are updated as numeric or alphanumeric which is incorrect, for any unmatched citycode it should return as "Mismatch".

Table 1

CityCode

RUH

`

`BEY

`IAH

`JED

`MUC

0

0RUH

0SHJ

0WOR

0YYC

1

1225

1430

1635

31-Oct

81434

AAA

AAE

AAL

AAN

AAP

Thanks

Gaurav

Table 2

CityCode	Country
AAA	FRANCE
AAE	ALGERIA
AAL	DENMARK
AAN	UNITED ARAB EMIRATES
AAP	UNITED STATES

[DataVitalizer · Accepted solution]

Hi @gauravnarchal 

Could you try this DAX formula this time, I tried it and appaently it worked

New column = CALCULATE (
 FIRSTNONBLANK ( 'Table 2'[Country], 1 ),
 FILTER ( ALL ( 'Table 2' ), 'Table 2'[CityCode] = 'Table 1'[CityCode] )
)

Did it work ? Mark it as a solution to help spreading knowledge.

A kudos would be appreciated

[selimovd]

Hey @gauravnarchal ,

try the following calculated column:

Country Lookup = 
VAR vLookupValue = LOOKUPVALUE('Table 2'[Country], 'Table 2'[CityCode], 'Table 1'[CityCode])
RETURN
IF(
    vLookupValue = BLANK(),
    "Mismatch",
    vLookupValue
)

For me this returns the following result, what I guess is what you want:

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[gauravnarchal]

Hi @selimovd  - I am still getting this error  "A table of multiple values was supplied where a single value was expected.".  Any other way to handle this?

[selimovd]

Hey @gauravnarchal ,

did you add it as calculated column and not as measure?

Otherwise can you share the file or at least some screenshots? This would help to find the mistake.

Best regards
Denis

[DataVitalizer]

Hi @gauravnarchal 

I duplicated your data sample in my power bi and a new column with the formula provided by @selimovd and it works correctly without errors.

[gauravnarchal]

Hi @selimovd  - Here is the screenshot of the error.

Below I have also shared the data for your reference to test.

Table 1 - Click Here

Table 2 - Click Here

Thanks

Gaurav

[selimovd]

Hey @gauravnarchal ,

can you please share the actual Power BI file?

I tried it with the data that you provided, that works. So there is something in your Power BI file that is different from mine.

Thank you and best regards

Denis

[DataVitalizer · Accepted solution]

Hi @gauravnarchal 

Could you try this DAX formula this time, I tried it and appaently it worked

New column = CALCULATE (
 FIRSTNONBLANK ( 'Table 2'[Country], 1 ),
 FILTER ( ALL ( 'Table 2' ), 'Table 2'[CityCode] = 'Table 1'[CityCode] )
)

Did it work ? Mark it as a solution to help spreading knowledge.

A kudos would be appreciated

[gauravnarchal]

Hi @selimovd - Here is the PBIX file.

Click here

Thanks

[selimovd · Accepted solution]

Hey @gauravnarchal ,

the problem is your data. You have multiple values for the city codes. For example:

The error message happens because there are not distinct values. For "AGA" what should the engine chose, United States or Morocco?

If you want a random of the two values (like the MIN, MAX or FIRSTNONBLANK) you can do that with the following calculated column:

Country New = 
VAR vCityCode = data[CityCode]
RETURN
CALCULATE(
    MAX(Country[Column2]),
    Country[Column1] = vCityCode
)

But be aware, the mapping is random. Your report will deliver once in a while different results.

So from my point of view the only solution is to fix the Country table and get rid of the duplicates or add another criteria to get unique combinations.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic