Forum Discussion
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
Bump - if there's no concrete answer to this, I'll need to attempt the same thing in MapBox.
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
Does anyone have a copy of the updated .JSON file with the corrected strings? I am really struggling trying to do this on my own.
It's attached to one of my posts further up. Can it still be accessed?
Has anyone had an issue with the FIPS with leading zeros not show up or vise versa? I am not sure if its a formatting issue - text or whole number? Depending on which one I use, it flip flops which FIPS show. I feel like I am missing something very simple.
Pic below is with FIPS as Whole Number
Pic below is with FIPS as Text
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.
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
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?
