Filled Map not allocating counties correctly (USA)

[Anonymous]

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?

[Anonymous · Accepted solution]

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]

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

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

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

[solutionsfinder]

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

[CahabaData]

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

[CahabaData]

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

[Anonymous]

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.

[CahabaData]

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

[Anonymous]

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