Pull custom shape map (topoJSON) keys into table or tooltip?

[Anonymous]

Hi!

Newbie here. I've got some data on carbon emissions by county in the state of Georgia and I've successfully found a topoJSON file to display a Shape Map (see below). 

When I view the map keys, I see this:

The valuable piece of information from the above is the name of the county - I am wondering if it is possible to somehow have the county name display in the map tooltip (and if I could add it as a value to the table in the visualization also, that would be amazing, if there was a way to have it match up the County FIPS code column from that table to the county name from the JSON file...there is not a county name column in the source data I am working with).

Thanks in advance!

[timg · Accepted solution]

Hi Bonnie, 

You can import your custom topojson file as a table and collect the values that way. Then you'll be able to use the key values in your visuals as well. 

1. go to getdata and choose the json source
2. import your topojson file
3. the tricky part, you have to navigate to the key information by expanding the records, and turning them into a table (check out a guide here: Using Power BI with JSON Data Sources and Files (mssqltips.com))
4. create a relation between the geoid in the new geography table and the county column in the countyresemi table

Once the above steps are done you should have your new geography table ready for use with your other table.

Below you'll find an example of when I import a similar topojson file for Dutch municipalities.

good luck!

[timg · Accepted solution]

Hi Bonnie, 

You can import your custom topojson file as a table and collect the values that way. Then you'll be able to use the key values in your visuals as well. 

1. go to getdata and choose the json source
2. import your topojson file
3. the tricky part, you have to navigate to the key information by expanding the records, and turning them into a table (check out a guide here: Using Power BI with JSON Data Sources and Files (mssqltips.com))
4. create a relation between the geoid in the new geography table and the county column in the countyresemi table

Once the above steps are done you should have your new geography table ready for use with your other table.

Below you'll find an example of when I import a similar topojson file for Dutch municipalities.

good luck!

[Anonymous]

Hey again Tim!

Ah thank you, that did work for extracting a useful table of FIPS codes + county names...the problem is, I'd love to be able to write a formula or something for each county where if FIPS = 13001 then county_name = Appling, or something to that effect. I'm trying to figure out how to avoid having to add a column to the source data of emissions with county name (as there are 37,000+ and counting lines in there). I guess maybe that formula writing needs to take place earlier in the source data creationg so it's bundled along with every row, rather than doing so at this stage...but thought it was worth exploring Power BI's capabilities!

[Anonymous · Accepted solution]

ha! got it! I just needed to head into the Relationships area and tell Power BI that these two tables are related by the FIPS code as the key. so a one-to-many relationship (I set cross filter to Both and checked make Active, for anyone else who winds up needing this solution). And ta-da! I was then able to drag County Name from the secondary table (extracted from the JSON as discussed above) into tool tips and Power BI mapped it all out quite nicely. Woo! Thanks everyone!

[timg]

Good to hear it worked out!

[HotChilli]

I think you'll have to get the county name into your data - it won't be picked up from the 'View map keys'.  Should be reasonably straightforward if you can find some FIPS <-> county data

[Anonymous]

I guess you're right! I guess I was hoping there was a way to write a formula or some other clever feature of Power BI where it could see a table of FIPS + county names and match those up with other columns of data I select, rather than me having to go add that column to that giant data source...ah well!