cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RMDNA Senior Member
Senior Member

Shape Map - unique map keys

Hi all,

 

I'm using a Shape Map visual using a custom TopoJSON for US counties (us-albers-counties via here). I have the correct format for the map keys. However, when I add county to the location field, it displays all counties with that name (e.g. all Douglas counties across the US), regardless of whether they're in my dataset or not. I've tried creating a field of a County and State combo (in various formats), but adding them to the location field blanks out the map.

 

counties.PNG

 

I've attached an excel of my data. Can anyone successfully get a map working with data at the unique state/county level?

 

1 ACCEPTED SOLUTION

Accepted Solutions
HotChilli New Contributor
New Contributor

Re: Shape Map - unique map keys

Hi,
I realise this is a late response and hopefully you have it sorted now but if you don't or someone else reads the thread, this might help.
Whatever field you pull into 'Location' for the shape map is the one that Powerbi uses to link to the map. I don't know how it chooses which field in the shape map to join to but there must be something clever going on. It probably has an algorithm that makes a best guess on data type and/or field name.
Obviously in this case 'county' is not unique in the shape map and so multiple counties get selected across the USA.

I tried with the supplied data set and pbi file. I added a new column combining state_fips and county_fips and padded the data where necessary to match the template XXYYY where XX is state fips e.g 05, 11 and YYY is the county fips e.g. 001, 012, 107.
We know that the us-albers-counties map has fips code in this format and that it is unique. I then pulled the new column into the 'Location' field of the shape map. This was only partially succesful. I added a slicer for State for testing and saw that not all the states were displaying appropriately.
I found that this is because of an issue with the us-albers-counties map provided. Some of the fips codes are strings and some are numbers.  So Powerbi can't make the link.

You can solve this by either getting a different USA county topojson map with the fips code in a consistent format OR as I did, edit the map in QGIS to make fips a string , export it as Geojson, convert to topojson and add it in to powerbi.  Make sure that the fips column you added earlier in powerbi is also a string.  This works.

As an aside, the 'view map keys' feature for shape maps isn't working in my desktop powerbi.  The window appears then it shrinks horizontally to about 1cm width.  Probably a bug.

 

Doug

View solution in original post

8 REPLIES 8
Highlighted
Community Support Team
Community Support Team

Re: Shape Map - unique map keys

Hi @RMDNA,

 

I made a test using the desktop of  version 2.60.5169.3201 64-bit (July 2018). And I created a same visual as yours. As your said “it displays all counties with that name (e.g. all Douglas counties across the US)”, did you mean that the grey ones should not be displayed any county name here? Actually it is not an issue, that is normal for the shape map here.

 

1.png

For more details, please check the pbix as attached.

 

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

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
RMDNA Senior Member
Senior Member

Re: Shape Map - unique map keys

Hi @v-frfei-msft,

 

My map dataset contains a subset of US states and their counties, so I'm looking for a view of all counties within only those states, regardless if the county names exists elsewhere in the US.

 

Below is a standard map visual showing exactly what I'm looking for, which I'm trying to recreate as a shape map:

 

Intent.PNG

RMDNA Senior Member
Senior Member

Re: Shape Map - unique map keys

Bump - if there's no concrete answer to this, I'll need to attempt the same thing in MapBox.

HotChilli New Contributor
New Contributor

Re: Shape Map - unique map keys

Hi,
I realise this is a late response and hopefully you have it sorted now but if you don't or someone else reads the thread, this might help.
Whatever field you pull into 'Location' for the shape map is the one that Powerbi uses to link to the map. I don't know how it chooses which field in the shape map to join to but there must be something clever going on. It probably has an algorithm that makes a best guess on data type and/or field name.
Obviously in this case 'county' is not unique in the shape map and so multiple counties get selected across the USA.

I tried with the supplied data set and pbi file. I added a new column combining state_fips and county_fips and padded the data where necessary to match the template XXYYY where XX is state fips e.g 05, 11 and YYY is the county fips e.g. 001, 012, 107.
We know that the us-albers-counties map has fips code in this format and that it is unique. I then pulled the new column into the 'Location' field of the shape map. This was only partially succesful. I added a slicer for State for testing and saw that not all the states were displaying appropriately.
I found that this is because of an issue with the us-albers-counties map provided. Some of the fips codes are strings and some are numbers.  So Powerbi can't make the link.

You can solve this by either getting a different USA county topojson map with the fips code in a consistent format OR as I did, edit the map in QGIS to make fips a string , export it as Geojson, convert to topojson and add it in to powerbi.  Make sure that the fips column you added earlier in powerbi is also a string.  This works.

As an aside, the 'view map keys' feature for shape maps isn't working in my desktop powerbi.  The window appears then it shrinks horizontally to about 1cm width.  Probably a bug.

 

Doug

View solution in original post

RMDNA Senior Member
Senior Member

Re: Shape Map - unique map keys

Hi @HotChilli,

 

I never did get it figured out, so this sounds promising! Could you share your edited topoJSON and sample PBIX? I'd love to give it a try.

HotChilli New Contributor
New Contributor

Re: Shape Map - unique map keys

Edited map and sample pbix

 

The CountyFips query is from your supplied data.  The other data is from the U.S. census (i was just using it to test the shape map).

 

I hope that helps.

 

Doug

geoffgarcia Frequent Visitor
Frequent Visitor

Re: Shape Map - unique map keys

Has anyone been able to get the 5 digit State/County FIPS codes to work on either a filled or shape (ideally) map?

I pulled the 5 digit codes from https://www.nrcs.usda.gov/wps/portal/nrcs/detail/national/home/?cid=nrcs143_013697

When I imported they defaulted to numbers but I updated the field to string.

Setting the FIPS code field as location or place each result in about 1-3% of the map being filled in.

Does anyone have any advice?

 

@HotChilli 

@RMDNA 

HotChilli New Contributor
New Contributor

Re: Shape Map - unique map keys

@geoffgarcia  You have everything you need in this thread (apart from your own data).  You need a shape map with consistent format FIPS codes (attached to one of my posts).

Don't set FIPS codes as Location or Place. The shape map doesn't care about things like that.

There is a sample pbix attached above that shows how it works.

.. and they fixed the 'View map keys' bug that I was referring to, so you can use that feature too.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)