Forum Discussion

gauravnarchal's avatar
gauravnarchal
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Need help on lookupvalue

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

 

CityCodeCountry
AAAFRANCE
AAEALGERIA
AALDENMARK
AANUNITED ARAB EMIRATES
AAPUNITED STATES

 

 

Need Help
Show and Tell
  • DataVitalizer's avatar
    DataVitalizer
    5 years ago

    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's avatar
    selimovd
    5 years ago

    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
     

9 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    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
     
    • gauravnarchal's avatar
      gauravnarchal
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      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's avatar
        selimovd
        Icon for Most Valuable Professional rankMost Valuable Professional
        5 years ago

        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