Shape map duplicate counties

[markjohnson952]

I am using the Shape Map with a TOPOJSON to identify sales by county.  In the cases where you have the same county name in multiple states (Jefferson, Orange, Washington) my sales numbers are all the same for those counties (Jefferson in Colorado has $10 in sales while Jefferson in Oregon also has $10).

How can I fix this in my PBIX or JSON?

Below is the link to the TOPOJSON file.

https://raw.githubusercontent.com/deldersveld/topojson/master/countries/united-states/lower-quality-...

Thanks!

[markjohnson952 · Accepted solution]

I figured it out.

You have to create a column concactenating the state fips code and the county fips code and then use that in your location field on visual.  You can also just add the list below in the link to your data model.

https://www.nrcs.usda.gov/wps/portal/nrcs/detail/national/home/?cid=nrcs143_013697

[Sumanth_23]

hi @markjohnson952 - can you also share a screenshot of your datamodel? Do your table setup have unique keys for each country?

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[markjohnson952]

Hello,

In my data, my counties are just listed as a text string.  Hennepin is just listed as Hennpin for the county.

[Sumanth_23]

hi @markjohnson952 - this could be the issue for the conflict as the visual would not be able to differentiate between counties with the same name in different states. You would need to setup a county key which is unique for State + County combination - in my opinion this should help resolve the issue. 

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[markjohnson952]

Hello,

I have tried using a different TOPOJSON which has the complete State name and County name which are both in my data model in Power BI and I have the same issue.  Below is the link to the file I am using.

https://raw.githubusercontent.com/deldersveld/topojson/master/countries/united-states/us-albers-coun...

[negi007]

@markjohnson952 I had faced similiar issues when i was working on one of my dashboard. I had same district name in multiple states of India. To tackle this problem, I renamed the district name for having same name in the JSON file like District_1, District_2. This wasy I was able to resolve this issue. In your case also try if changing the name of Jefferson, Colorado to "Jefferson, Colorado" and same way change the name for other county. Give it a try for few counties. If it works. you may do it for all counties. Let me know if it helps you.

[markjohnson952]

Hi @negi007 

I have tried to change the name and also format to correct the issue but it still persists.  

Below is a link to a PBIX file from a similar thread which displays the same behavior.  Try Lincoln county in Nevada or Jefferson county in Colorado.

https://www.dropbox.com/s/ktsykfhe9tfmdtd/Shape%20Map%20-%20unique%20map%20keys2.pbix?dl=0