cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bonnie447
Frequent Visitor

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

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). 

Bonnie447_2-1612815857076.png

 

 

When I view the map keys, I see this:

Bonnie447_1-1612815560835.png

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!

 

 

2 ACCEPTED SOLUTIONS
timg
Solution Specialist
Solution Specialist

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.

1.PNG

good luck!

View solution in original post

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!

View solution in original post

6 REPLIES 6
timg
Solution Specialist
Solution Specialist

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.

1.PNG

good luck!

View solution in original post

Bonnie447
Frequent Visitor

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!

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!

View solution in original post

timg
Solution Specialist
Solution Specialist

Good to hear it worked out!

HotChilli
Super User II
Super User II

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

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!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors