Forum Discussion

MarcUrdang's avatar
MarcUrdang
Post Patron
6 years ago

Country/Region

I can't seem to find the Country/Region option in the data drop down box thanks Marc
Need Help
MarcUrdang's avatar
MarcUrdang
Post Patron
6 years ago

Hi again Amitchandak

 

I have 2 fields: First Leg Load Country (2 digit) and First Leg Load Port (3 digit). I couldn't get Bling to work them into a map so downloaded a file .. you will see a third data source with all country and city codes. It then says to change two filds in that file to GEO fields from text. But I can't find that option in the dropdown box.

 

also I still need to find a solution on the previous topic we spoke about ... the solution you supplied didnt work .. perhaps we can revisit also?

 

https://drive.google.com/open?id=1Wd-VzWUZxs42fbIrWxxi3MJrcevZmZEu

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi MarcUrdang ,

 

We can add a calculated column using lookupvalue from the third table.

 

 

Region =
LOOKUPVALUE (
    'Officially assigned code elements[edit]'[English short name (using title case)],
    'Officially assigned code elements[edit]'[Alpha-2 code], 'Consol INBOUND'[First Leg Load Country],
    'Officially assigned code elements[edit]'[Alpha-3 code], 'Consol INBOUND'[First Leg Load Port]
)

 

 

Then use this region as the x-axis, but in you sample data, CN and Qin cannot find any name in third table. we can change to use this formula.

 

Region =
VAR t =
    FILTER (
        'Officially assigned code elements[edit]',
        'Officially assigned code elements[edit]'[Alpha-2 code] = [First Leg Load Country]
            && 'Officially assigned code elements[edit]'[Alpha-3 code] = [First Leg Load Port]
    )
RETURN
    IF (
        COUNTROWS ( t ) = 0,
        MAXX (
            FILTER (
                'Officially assigned code elements[edit]',
                'Officially assigned code elements[edit]'[Alpha-2 code] = [First Leg Load Country]
            ),
            [English short name (using title case)]
        ),
        LOOKUPVALUE (
            'Officially assigned code elements[edit]'[English short name (using title case)],
            'Officially assigned code elements[edit]'[Alpha-2 code], 'Consol INBOUND'[First Leg Load Country],
            'Officially assigned code elements[edit]'[Alpha-3 code], 'Consol INBOUND'[First Leg Load Port]
        )
    )

 


BTW, pbix as attached.

 

Best regards,

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

  • MarcUrdang's avatar
    MarcUrdang
    Post Patron
    6 years ago

    So sorry .. I didn't see your previous message .. however after creating this new column the text 'Officially assigned code elements[edit]' is highlighted in red as it cannot be found ... how do I rectify this?

     

    thanks

    Marc

    • v-lid-msft's avatar
      v-lid-msft
      Community Support
      6 years ago

      Hi MarcUrdang ,

       

      You may need to adjust the table name in the formula. Please refer to the sample pbix file in my previous post

       

       

      Best regards,

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

      • MarcUrdang's avatar
        MarcUrdang
        Post Patron
        6 years ago

        Good morning .. this seems to work thank you very much. However I only see the country name ... I also need to see the Port name .. ie the 3 digit code with, if possible, its corrosponding name?

         

        Can you help with this?

         

        thanks so much,

        Marc