Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
bwarner87
Advocate I
Advocate I

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 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.

 

 

 

1 ACCEPTED SOLUTION
bwarner87
Advocate I
Advocate I

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

View solution in original post

3 REPLIES 3
bwarner87
Advocate I
Advocate I

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
Advocate I
Advocate I

@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. 

 

bwarner87_0-1663098514645.png

 

v-xiaotang
Community Support
Community Support

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors