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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Filled Map not allocating counties correctly (USA)

Hi,

 

I'm trying to plot US counties using the power BI filled map and ran into some problems. I found on the forums that the best way to input counties into the Location field is with it's associated state so Bing won't get confused. Makes sense, this is the format of my County column: 'De Kalb County IL' which is De Kalb county Illinois.

 

This works brilliantly in almost all circumstances however I've just found that Louisiana, LA is not working 100% correctly!

 

Has anyone else experienced this / knows a better way to tell Bing what County to map?

 

Filled_Map_COunty.png

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I've found the problem with Louisiana.

So, as suggested in previous posts working with bing maps and arcgis maps, the best format for counties is the following string:

 

"<County>, County, <State Code>"

 

And in this case Bing and arcgis manage to allocate nearly all of the counties in the USA perfectly correctly.

 

However, it really struggles with Louisiana specifically and I've found out why:

 

Louisiana doesn't have counties. Since Louisiana was roman catholic when controlled by both France and Spain, instead of counties it used Parishes to divide its land. So the solution to the problem to Louisiana is the following:

 

IF State code = "LA"

"<County>, Parish, <State Code>"

ELSE

"<County>, County, <State Code>"

 

and this works perfectly!

 

What a sneaky nuance which is incredibly annoying from a mapping BI perspective but also very interesting.

 

Will

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

I've found the problem with Louisiana.

So, as suggested in previous posts working with bing maps and arcgis maps, the best format for counties is the following string:

 

"<County>, County, <State Code>"

 

And in this case Bing and arcgis manage to allocate nearly all of the counties in the USA perfectly correctly.

 

However, it really struggles with Louisiana specifically and I've found out why:

 

Louisiana doesn't have counties. Since Louisiana was roman catholic when controlled by both France and Spain, instead of counties it used Parishes to divide its land. So the solution to the problem to Louisiana is the following:

 

IF State code = "LA"

"<County>, Parish, <State Code>"

ELSE

"<County>, County, <State Code>"

 

and this works perfectly!

 

What a sneaky nuance which is incredibly annoying from a mapping BI perspective but also very interesting.

 

Will

Anonymous
Not applicable

Hello together,

 

just have read this post and thought I've found someone with the exactly same problem, but in comparison to the described problem I've lot of US counties in wrong US states.

 

I would like to visualize a kind of heatmap for the unemployment rate of all US states and counties.

Source for US Unemployment Rate States: https://www.bls.gov/web/laus/laumstrk.htm

Source for US Unemployment Rate Counties (just the latest): https://www.bls.gov/web/metro/laucntycur14.txt

 

With the states everything is fine, like this: (data category = "state or province")

 

2018-01-30 13_03_37-HR Monitor - Power BI Desktop.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

But with the counties it looks like this: (data category = "county", all counties named like "Anderson County, SC"

2018-01-30 13_09_39-HR Monitor - Power BI Desktop.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I've already tried to describe the county rows like mentioned with "<County>, County, <State Code>", but there's no other result.

 

Could help me someone or is this a Bug with Bing/Microsoft?

 

Thank you very much!

 

BG,

 

Sven

I have the same issue and I just can't seem to find what is triggering the selection of multiple counties with the same name.. 

wow - good post....  I knew they had parishes but just assumed if you called them counties everything would work ok.......

 

 

www.CahabaData.com
CahabaData
Memorable Member
Memorable Member

I have not done this - so my input is just a guess at this point.  But my understanding is that for mapping one should have a column explictly named i.e.: City   County   State   zipcode  Country

 

that the engine that drives mapping recogizes those terms from your field names and will rely on them to do the mapping.

 

I say this in seeing past info about mapping by zipcode  that was pulling in the wrong country - and was resolved by adding a country field (because the same zip code is used in differing countries....)

 

in your post it looked like you merged country & state in one field and so that makes me think they should be separate fields

 

 

www.CahabaData.com
Anonymous
Not applicable

Hi,

 

Thanks for your reply, I do indeed have them as separate fields, all categorized correctly but using both of those fields produced a lot more errors. In my search on the Power BI online forums, someone found a solution where they merge the columns as described above (original post here https://community.powerbi.com/t5/Desktop/Filled-Map-Issues/m-p/209117#M92226) . This worked much better and I thought it was perfect until I realised that Louisiana is still messed up.

 

Even the ArcGIS map has the same problem with Louisiana and errors for every county in that state. 

 

Apart from LA, everything else looks great.

In that case you have to wonder if it is the data itself

 

You might sample some of the wrong LA county records and perhaps they are having addresses that are not correctly aligned to their true county......   if everything else is correct it would seem this is possible but..... 

www.CahabaData.com
Anonymous
Not applicable

I came to the same conclusion and looked up some of the counties. The one highlighted in the picture above, Webster County LA is simply a county in Louisiana. There's nothing unusual about it, or its naming convention or anything. I've also tested a couple of others with no success. It's all very odd, although I my best guess is still that there is something up with Counties in LA in either the data or how the map reads the data

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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