cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver II
Resolver II

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

Accepted Solutions
Resolver II
Resolver II

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

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

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

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

Resolver II
Resolver II

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

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

 

 

www.CahabaData.com
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

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors