Forum Discussion

bwarner87's avatar
bwarner87
Icon for Advocate I rankAdvocate I
4 years ago
Solved

Bing Map - World view with a Location Hierarchy including State/Province Option

Hello Fellow PBIers,    Challenge Summary: In short I'm building a world view map of staff locations using bing maps (i.e. "Map" visual icon) WITHOUT latitude and longitude information.  The goal i...
  • bwarner87's avatar
    4 years ago

    I found a solution that works for me. After re-reading THIS,  I decided to create a calculated column titled Place with a conditional statement to send different combinations (3digit countr code OR city, 3 digit country code, OR city, state/province, 3 digit country code) to location services. I also gave the calculated column the "Place" data category designation.

     

    I then use Country and Place (new calculated column) as the locations. When i'm at the Place hierarchy level, It now more accurately maps the staff that are in countries with state province to the right location (i.e. Hollywood, Florida, USA VS Hollywood, California, USA) and let's me see staff in cities without a state/province. (i.e. Rome, Italy)

     

    Hope this works for others!

     

    Here's my rough DAX which could probably be made more efficient with a SWITCH function. 

     

    Place = 
    IF(ISBLANK('Master Employee Data'[City]) || 'Master Employee Data'[City] = "",
        'Master Employee Data'[Country],
            IF(ISBLANK('Master Employee Data'[State/Province]) || 'Master Employee Data'[State/Province] = "", 
                'Master Employee Data'[City] & ", " & 'Master Employee Data'[Country],
                    'Master Employee Data'[City] & ", " & 'Master Employee Data'[State/Province] & ", " & 'Master Employee Data'[Country]
            )
    )