Filled maps, US counties, and Louisiana

[Anonymous]

Hi all,

This is a post to demonstrate an interesting nuance I found when mapping US counties.

The problem I was facing was that some counties were not getting mapped correctly by the Power BI visual: Filled map OR by Arcgis maps. I then found from the forums the best format for my county column:

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

With this format the filled map managed to place NEARLY every county in the USA. Unfortunately, I was still having real trouble with Louisiana. Not only were some of the Louisiana Counties not being mapped but worse still they were being allocated to the wrong state, e.g. "Lafayette, County, LA" was being plotted in the middle of Georgia! Nightmare.

After some investigation I found something interesting. Louisiana was controlled by France and Spain during it's infancy and was in both circumstances was a roman catholic state. As such the subdivisions of the state were not counties but instead Parishes. And this has never changed so today, Louisiana is the only state to not have Counties but to have Parishes... therefore:

IF( State code = "LA",
     "<County>, Parish, <State Code>",
     "<County>, County, <State Code>"
)

And all of a sudden it works perfectly! Louisiana is nicely filled in.

Hope this helps some people, certainly stumped me for a while.

Will

TL;DR

Mapping counties in the US requires this format for bing maps:

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

BUT Louisiana doesn't have counties, it has Parishes so for Louisiana you need:

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

[pbiwizard]

Hi! Would you be able to share is this is a new column or a measure? I am having trouble with this same issue. I am fairly new to PBI and am unsure what the "<" symbols and what the formula you made means. Thanks! 

[TomMartens]

Hey,

I just put this little piece of information to my mapping onenote page, and will hopefully never forget this information.

But I'm wondering why you have a column called Parishes in your table and what are the values for this column and do they make sense.

Regards

Tom

[pbiwizard]

Hi do you think you could explain the solution the user above mentioned? Thanks! Having the same issue with Louisiana parishes. 

[TomMartens]

Hey @pbiwizard ,

my understanding back then was, that a column needs a proper value like this one
"Lafayette, County, LA"
using a Power Query function the value above was transformed to 
"Lafayette, Parisch, LA"

This with assigning the correct data category to the column,

will avoid (most probably) weird data visualizations:

Hopefully, this adds some explanation, and helps to tackle your challenge.

Regards,

Tom 

[pbiwizard]

This worked amazing! All of the parishes are now showing up on the map correctly! 

[TomMartens]

😉 Perfect!

[Anonymous]

Hi, my data is on buildings in the US and for one particular visual I want to sum them up to state and county level, e.g. total value of buildings in Washington or Total value in Lafayette county Louisiana.

Fortunately each building comes with its respective state and county and all i needed to do was get bing to understand what county i was referring to for each building. The logic I showed in the original post was how I achieved this. 

Thanks for your reply,

Will