We’ll look to make this more public soon. Awesome initiative
Looks awesome @BrianJ
Updated with my info… Great idea!
Thanks to everyone who entered their data, but no additional entry is needed. I’ve now gotten access to a comprehensive dataset with thousands of eDNA records. Version 2 of this report coming later this week and with 100x more info, it’s a lot more interesting.
It is a great idea!
I’d be very happy to see my Country on this Map, too.
@BrianJ great idea! Greetings from California!
Just a brief update on this project. I completely re-did the report above with the full membership data, and with @Paul’s expert guidance, moved from the simple circle map native visual to the MapBox custom visual, which allowed for better visualization and much more interesting exploration of the data. However, right near the end, I realized that Mapbox does not support report tooltips, which are a big part of what I wanted to incorporate into this report. Soooo…again with @Paul’s help, I am now migrating the report into a second custom visual called Icon Map, which does support report tooltips and has even more powerful visualization features.
I am pretty much a GIS neophyte, so while this has been a great learning experience for me (and one I’ll share with you re: a Data Challenge-type writeup), it has taken me a lot longer than anticipated to navigate (no pun intended) the fascinating new world of geojson files, “well known text”, choropleths, geocoding and reverse geocoding, ISO country codes and much more. So, thanks for your patience and hope to have the report out soon.
P.S. @Paul’s invaluable assistance on this project has given me a detailed preview of the geospatial coursework he’s preparing for the Enterprise DNA platform in 2021, and I can say without hesitation that it’s going to be incredible. More to follow on this soon…
Okay, here we go - first release of the revised report with all the member data included. This has been a fantastic team effort, with enormous thanks first to Enterprise DNA Expert and Geospatial Maestro @Paul, without whose expert, generous and patient guidance I would still be banging my forehead against my desk trying to get this report to work. Also huge thanks to @sam.mckay, @joanna and Shiela Martinez of the @EnterpriseDNA team, for their enthusiastic support of this effort and providing access and regular updates to the underlying data. Finally, a tip of the cap to @datazoe for her inspiration on report tooltips via her outstanding Data Challenge entries.
Without further ado, here’s the publish to web link and screenshots of the major pages:
There are still a number of issues I want to address on this, including tapping the incredible design skills of the expert team to try to spiff up the design a bit, and also work on getting the play axis to show shading reflecting the contemporaneous quartile values, rather than just the final values.
While this report looks pretty straightforward, there’s actually a LOT going on behind the scenes, and I am in the process of drafting a detailed Data Challenge-type writeup that I’ll post this week to provide more insight into how I built this for those who might be interested (basically a diary of “things I learned from Paul” ).
I hope you enjoy exploring this - it really shows how rich and interesting the analysis of geospatial data is, even in a pretty basic report like this one.
Great idea. Filled in the form!
Congrats again @BrianJ - looking great!
For those who are interested, here’s how I created the above report. It’s unlike any report I’ve ever created before, in that the most difficult part of it was the creation of a single dimension table – the Countries table in my data model. Detailed below are the individual steps and sources I used to create this table, and afterword are some additional elements that I thought also warranted some more detailed explanation.
To begin this analysis, I needed a base data layer that would define the country boundaries on a map. One of the most commonly used formats for this type of layer is GeoJSON. According to the GeoJSON specification:
> GeoJSON is a format for encoding a variety of geographic data structures […]. A GeoJSON object may represent a region of space (a Geometry), a spatially bounded entity (a Feature), or a list of Features (a FeatureCollection). GeoJSON supports the following geometry types: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection. Features in GeoJSON contain a Geometry object and additional properties, and a FeatureCollection contains a list of Features.
While IconMap can ostensibly support GeoJSON files, I had difficulty getting it to accept this file type. However, I found the “Well Known Text” (WKT) format worked perfectly, and as an additional benefit, WKT geographic information stores locally in Power BI when using IconMap, as opposed to connecting remotely as is the case for GeoJSON files. The problem is that for complex boundaries, WKT entries can be millions of characters, which is a problem since Power Query can only save a maximum of 32,766 characters in a single field. Thus, without modification, the Canadian coastline for example would take 67 separate columns to store in WKT format. However, that level of resolution was not needed for this map, so I was able to simplify the boundary file significantly, reducing its size such that only four countries had WKT entries that exceeded the maximum field length, and Canada needed only 4 columns. I dealt with this using the process detailed below. However, if full resolution was needed, custom M could be written to split the text into segments, placing each segment in a separate column and then unpivoting the data to allow it to be easily reassembled using a DAX CONCATENATEX structure.
Countries Dimension Table Creation:
Download Countries geojson file:
Using Mapshaper.org, simplify the geojson file using the program defaults. Export to geojson format.
Using mygeodata.cloud, convert geojson format to WKT (well known text). Will export to CSV format.
Pull CSV into Power Query.
Duplicate WKT field and transform to Length. Even after simplification in MapShaper, four countries (Canada, Russia, Indonesia and US) have a WKT field length that exceeds the maximum that can be saved in Power Query of 32766.
To get around this limitation, we use the Split by Position transformation (0, 30000, 60000, 90000) to create four separate columns of WKT data, which then can be concatenated back together using DAX.
Filter resulting file to eliminate Antarctica and any other countries with no ISO 3 code.
Using the Web Connector, download the full list of ISO 2, ISO 3 and Numeric Country Codes from https://www.iban.com/country-codes
Do a full left outer join in Power Query of the WKT table created in steps 1-7 with the ISO list table created in 8) above to add ISO 2 to the dataset. Will be critical later for adding other necessary data for the analysis.
Downloaded country lat long JSON dataset from:
Did left outer join on ISO 3 with Country table from 9) to add latitude and longitude fields
- Added flag icon URLs from flagpedia.net using the following structure:
(Duplicated ISO 2 field, transformed to lower case and used Add Column from Example to replace “bh” with the proper ISO 2 code for each row.
- Downloaded country population (2019) by ISO from United Nations Population Dynamics page:
Did left outer join on ISO 3 with Country table from 11) to add 2019 country population (in order to calculate eDNA members per capita and per 100,000 people)
- Downloaded continent by ISO from https://www.geonames.org/countries/. Also included capital city, country area (sq. km) and population
Did left outer join on ISO 3 with Country table from 12) to add continent code, capital city, population (to calculate members per capita and members per 100,000 people) and area.
Added Column by Example for Continent Name, based on continent code.
Filtered out Continent = “AN”
To handle records from the Members file with no Country Code (ISO 2), created a record in Excel using the structure from the Countries table. Assigned the codes XX and XXX to “Unknown” and appended that record to the Countries table. In the membership table replaced all blank country codes with XX to create referential integrity.
17.Close and Apply and then set Flag URL field to Image URL data category, and Latitude and Longitude to those respective data categories as well.
OTHER NOTEWORTHY ISSUES:
Country Flag Icon in Report Tooltip – I thought it would be a cool effect to have the country flag change dynamically within the report tooltip as you moused over different countries. To display the image URL of the flag from the countries dimension table in the tooltip, I used a custom visual called Simple Image that I also used in Data Challenge #5 for the same purpose. This is necessary, since I believe the only native visual that can display image URLs is the table/matrix, and given formatting limitations that is not an ideal way to do so. Simple Image provides a better look and more extensive formatting control.
Active Dates in Date Slicer – this question comes up fairly frequently in the forum: how to only show the relevant dates (i.e., those contained in the fact table) from the date table in the date slicer up to the current date and have that dynamically update each day. To do so I just created the following calculated column in my Dates table:
Active Dates =
VAR MinDate = DATE( 2016, 9, 23 ) VAR MaxDate = TODAY() VAR Result = IF( AND( Dates[Date] >= MinDate, Dates[Date] <= MaxDate ), 1, 0 ) RETURN Result
and then set the Filter Pane to only show dates where Active Dates =1.
Quartile Calculation – I thought a meaningful way to visually depict number of members in each country would be to calculate by quartile. This allows relevant comparisons across groups, while dealing with the challenge of the United States being an enormous outlier, as well as the large number of countries with just one or two members. Here’s how I dynamically calculated quartiles using a helper table created via DAX expression:
FILTER( ADDCOLUMNS( SUMMARIZE( Countries, Countries[ISO 2] ), "TotMem", [Total Members] ), [TotMem] <> BLANK() )
Quartile Members =
VAR SelValue = SELECTEDVALUE( Quartiles[TotMem] ) VAR FirstQ = CALCULATE( PERCENTILEX.INC ( Quartiles, Quartiles[TotMem], .25 ), REMOVEFILTERS( Quartiles ) ) VAR SecondQ = CALCULATE( PERCENTILEX.INC ( Quartiles, Quartiles[TotMem], .50 ), REMOVEFILTERS( Quartiles ) ) VAR ThirdQ = CALCULATE( PERCENTILEX.INC ( Quartiles, Quartiles[TotMem], .75 ), REMOVEFILTERS( Quartiles ) ) VAR Result = SWITCH( TRUE(), SelValue <= FirstQ, 1, SelValue <= SecondQ, 2, SelValue <= ThirdQ, 3, 4 ) RETURN Result
- Dynamic Time Filtering of Quarterly Bar Chart – for the bar chart at the bottom of the screen, I only wanted to display the last five years (20 quarters) of data, but do so dynamically. Use of the quarter offset field in @Melissa’ s awesome Extended Date Table makes this a snap. In the filter pane for that visual, just set as follows:
Overall, I’m pretty happy with the way this turned out. A few things I need to address in future updates:
a) update geojson file for post-Brexit conditions
b) I tried to make the play axis animation cumulative rather than period by period. However, after extensive research and far too many hours of experimentation, I don’t think this is currently possible with this visual (hoping someone more clever than me proves me wrong though). As the next best alternative, I want to make this animation show the quartile shading contemporaneous with the quarterly selection, rather than corresponding to the final quartile calculation.
c) Design is not my strong point, and I think the slicer panel in this design is clunky and takes up too much room. @Mudassir was generous enough to do a complete redesign that is so far superior to my original design that if he weren’t such an incredibly nice guy, it would have seriously hurt my feelings. I will be posting version 1.6 – Mudassir Extreme Makeover Edition later this week.
I hope you found this useful. Again huge thanks to @Paul and @Mudassir for their assistance.
So here’s the redesign of my report done by @MudassirAli. I think you will agree that he absolutely knocked this out of the park with a gorgeous redesign. In addition to just looking much more attractive and polished, his design also freed up a substantial amount of space at the bottom, allowing me to add my favorite custom visual – the Queryon Timeline to show in chronological order the last 10 countries that joined. Along with this I also implemented an additional report tooltip to provide more information about these last 10.
Huge thanks to Mudassir for contributing his time, effort and incredible talent to this project.
Kudos to @BrianJ for successfully completing a very complex report and executing Maps Visulaization perfectly. Not to mention the Queryon Timeline visual that always steals the show. This looks like a simple report but a lot of hard work has been put into it as handling maps in PBI has always been the painful task.
@BrianJ’s efforts were 99% and mine was only 1%.
I am struggling to build the countries dimension table, using mygeodata.cloud, convert geojson format to WKT (well known text) and to export into CSV format.
Is it possible to share the “countries_simplified.csv” or the location in Enterprise DNA from where I can download it.
Sure – requested file is attached below. Great exercise to rebuild this report from scratch – I learned an absolute ton about geospatial analysis in Power BI when I put it together. The WKT aspect was one of the more difficult elements of this report, because even simplified, the polygon data for the larger countries overran the characters per cell limit in Power Query, and had to be stitched together from multiple columns using DAX.
Good luck! Just give a shout if you need anything else.
countries_simplified.csv (1.2 MB)
Hi @BrianJ ,
Thanks for the quick response.
I completely agree that the amount of efforts which you have put down to tailor such a nice dashboard with lots of stuff under the hood.
Your works are really so amazing and its such an inspiration the way you bring in the possibilities and makes us way to learn more and hats off for the tremendous amount of dedication and that is obvious in these dashboards.
Once again thanks for the amazing insights/analysis which you bring in on the dashboard using the R models and really really happy for the support