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

[bwarner87]

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 is to allow a user to navigate via drill down from Country -> State/ Province -> City and still see indication of a staff member at the City level. However when I include state/province as a location and attempt to drill down say for Argentina (Argentina -> [blank] -> Buenos Aries) it shows results at country level, no results at State / Province level and no results at city level. I want it to ignore a blank state/province where blank and bing should get the city/country combo and then allow the city to show when I go down to city level. 

What I've done so far:

• Followed advice here -> and here 
• Fields I have in data model with data type = Text and Data Category designations (DC) listed below
  • Address 1 and Address 2
    • DC = Address
  • City
    • DC = City
  • State/Province (2 digit code (i.e. GA for Georgia, United States)
    • DC = State or Province
  • Country (3 digit country code (i.e. CAN for Canada)
    • DC = Country/Region
  • ZIP (i.e. 23455 for USA)
    • DC = Postal Code
  • Place (Calculated Column = concatenated [City] [2 digit state code] [3 digit country code]
    • DC = Place
• In my Bing "Map" visual
  • location
    • Country
    • State/Province
    • City
  • Bubble Size
    • Measure = count of staff IDs
• If I remove "State/Province" in the list of location fields, then I have an issue in the US where Duluth Georgia shows up in Duluth Minnesota when I'm viewing at the City level because I haven't given "State/Province" to make it less ambiguous in the bing process for location identification. So i need to satisfy use cases for countries with or without State/Provinces when drilling down to City. 

thanks in advance and let me know if you need more information.

[bwarner87 · Accepted solution]

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]
        )
)

[bwarner87]

@v-xiaotang ,

See screen shot of issue using very simple setup. The count is simply selecting count for the employee ID field. It's NOT a measure. 

[v-xiaotang]

Hi @bwarner87 

Thanks for reaching out to us.

>> However when I include state/province as a location and attempt to drill down say for Argentina (Argentina -> [blank] -> Buenos Aries) it shows results at country level,

so it should show results at city level when you drill down from  State/ Province -> City, right?

could you share a sample file and the right results when visual is at city level? so that we can troubleshoot the measure code. Thanks.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.