I am super excited to see that the custom visual Icon Map is now available to use within the Power BI Market Place. For full instructions on how to use it, please visit https://icon-map.com/
Don't be fooled by the name Icon Map as this visual helps you create many more mapping features besides icons!
A powerful part of this custom visual is that it draws polygons stored as 'WKT' strings which reside within the data model. Because of this, you have much greater control with what is displayed on the map, when its displayed and how its displayed. As well as this, the visual benefits from the ability to add icons (hence the name icon map) which are displayed on the dashboard using Latitude and Longitude fields.
Firstly, I will show you how to add data in order to view a map:
Icon map Requires the following:
Category This is the 'Detail', and every unique Category will generate 1 point OR polygon on the map.
Size This is a measure which may reflect the 'size' of each point (You will see later how you can override this with a fixed size, however, size is still mandatory)
OPTIONAL DATA FIELDS WHICH WE WILL BE COVERING IN THIS BLOG:
Latitude / Longitude - If you would like icons on your map, you will need to populate Longitude and Latitude from your dataset. If you would like to only have a Choropleth map, Latitude and Longitude must be left empty.
Where Latitude and Longitudes exist on the row, a point/icon will be rendered. So if one row contains both LAT/LONG and WKT formatting, LAT/LONG will take precedence.
You will also see Destination Longitude and Destination Latitude. If you use this, you can make a line which I will not be covering in this blog.
All in the Formatting
So where are all the sophisticated mapping tricks? Welcome to the the formatting tab - this tab lets you intricately tailor the map to be exactly what you need. You can do things like specify a background map, make the background map dynamic based on a measure, connect to third party mapping solutions, change colours/sizes and much more. However, The two most important formatting areas in this blog are:
Objects Where you assign your polygons and icons - REMEMBER - each row represents one point/Icon OR one Polygon. We will call the icons/polygons marks. A WKT string will need to be declared when a polygon is displayed (without lat and long), and a URL will need to be declared if an icon is to appear (with lat and long). The best way to declare this is using a measure to provide the information for each row. Some of the options within the objects area are only relevant to certain types of marks - i.e size behaviour and Image Width/Height are only applicable to rows that have icons. The visual will evaluate every row grouped by whatever you added into the 'Category' area in order to determine what options are relevant.
Formatting How you are going to display the chloropeth in terms of dynamic colouring and lines. The formatting will only be relevant if an icon is not declared - so either a point with Lat and Long but without an image URL OR no point but a polygon's WKT string. For this exercise i have created a measure called COLOUR which will lead the gradient colouring for each polygon.
Preprocessing
We need to make sure we have all the data in the correct format in order to visualise the map. So lets go back to WKT strings - what are they and how will it draw your polygons?
Below is an example of a polygon in 'Well Known Text' format:
You have a series of decimal pairs - each pair being a latitude and Longitude to form a point. All the pairs (or points), make one polygon. This is then stored as a string in a single field - and each polygon needs to be stored in a separate row.
Adding a field with WKT data can be as simple as dragging and dropping a geography column from a SQL Database - or sometimes (Depending on how the geography is stored), you may need to convert it to a string. This is as simple as the following SQL syntax:
SELECT GeographyField.ToString() AS WKT FROM MYGEOGRAPHYTABLEIf you have a shape / KML file, you can convert it to WKT using applications such as the free tool QGIS. QGIS also allows you to export the results into Azure SQL where you can then visualise the results in power bi.
Because WKT is a string type rather than a geography type, you can ingest this format into Synapse, allowing you to combine with a central geography hierarchy structure which is exactly what I have done.
In order to take full advantage of mapping data, the geospatial data needs to be pre-processed such as:
Geocoded to the right level of granularity.
Applications such as QGIS (free to download - https://www.qgis.org/) can do this. And this is how I used QGIS for this simple scenario: -
I needed to add a Local Layer Output Area code to every row that corresponds with each row's Latitude and Longitude - this is a table containing football stadiums within the UK
Solution
I simply imported that data as 1 layer and the LSOA Shape file as a second layer (which I downloaded from the UK Open Geography Portal Open Geography portalx (statistics.gov.uk)) . I used the intersection processing tool to apply the spatial join.
This took some time to process even with a small dataset - so for scalable productionized pipelines at enterprise scale with fast completion times, I recommend using a Spark Engine such as a Synapse Spark cluster. Mike Robson (mirobson@microsoft.com) has created an amazing scaleable geoprocessing solution which proved to be a lot faster than any client based application. He will be sharing his work in due course!
Adding WKT strings into the dataset
I solved this with the use of shapefiles and then Importing them into an Azure SQL Database. As I had QGIS installed for the previous use case, I used this as a quick way to import the shape file. QGIS has a convenient way to import the geography data into SQL Server using one of the processing tools.
After I processed the data which is now stored in a geospatial database, I then ingested it into Synapse in order to analyse this alongside many other datasets within Power BI.
Modelling the Data
Create a Good Geography Dimension.
I have created a Geography Dimension which encompasses LSOA Codes, District Codes and all the corresponding WKTs. This is IMPORTANT! - do not put WKTs in your fact table - they will need to be assigned to VARCHAR(MAX) datatypes which are very expensive in size. So make sure you only have unique values per row for each WKT.
Going back to a really important point around icon map - it will only render one mark per row. So if you need to see 7 marks (whether its a point, polygon, icon, line etc) you will need to generate 7 rows. Hopefully you will see that modelling the data to handle this is very important.
My Use case
I want to display a chloropeth of dynamically selectable measures (I am using stop and search and crime data) and need to show this by a dynamic boundary (the user will either select district or LSOA level).
I want to display where the football stations are on a map using a relevant icon
I want to display where the train stations are using a relevant icon. All of this needs to be displayed at the same time.
In order to achieve this I created a table in synapse (could have been a materialized view), in order to generate a map that meets the 1 row 1 point/polygon requirement.
You will note that I am unioning the data as apposed to joining. By stacking all the items like this, I will be able to create my map. The table combines rows from stations, LSOA boundaries and football stadiums. If you need more layers to the map, you union more data.
PLEASE NOTE. you will not be unioning detailed fact tables - the detail will only go as far as the number of marks that will be rendered on the map. Any detail will be referenced using other tables within the model.
Below is the SQL I used to generate a scafold map table.
CREATE TABLE dbo.MAPPINGCRIMESSTATIONS
(
ID INT IDENTITY(1,1) NOT NULL,
Type INT NULL,
Dettype VARCHAR(60) NULL,
Station VARCHAR(50) NULL,
Stadium VARCHAR(50) NULL,
Latitude [decimal](38,18) NULL,
Longitude [decimal](38,18) NULL,
Year VARCHAR(4) NULL,
Entries_Exits INT NULL,
Interchanges INT NULL,
Capacity INT NULL,
LSOAID INT NULL,
LAD17CD VARCHAR(12) NULL
)
WITH
(
DISTRIBUTION = HASH ( LSOAID ),
CLUSTERED COLUMNSTORE INDEX
)
;
INSERT INTO MAPPINGCRIMESSTATIONS
--THIS WILL GENERATE ROWS FOR ALL TRAIN STATIONS
SELECT
1 as Type
,'S- ' + a.Station as Dettype
,a.Station
,null as Stadium
, a.Latitude
, a.Longitude
, a.Year
, a.Entries_Exits
, a.Interchanges
,null as Capacity
, b.LSOAID
, null as LAD17CD
FROM BECKYWORK.STATIONS a
INNER JOIN BECKYWORK.PostcodeFact b ON
b.pcd7 = a.Postcode
UNION
--THIS WILL GENERATE ROWS FOR ALL LSOA BOUNDARIES (to use for chloropeth)
SELECT
2 as Type
,'LA- ' + LSOA11CD as Dettype
,null as Station
,null as Stadium
,null as Latitude
,null as Longitude
,null as Year
,null as Entries_Exits
,null as Interchanges
,null as Capacity
, ID as LSOAID
,LAD17CD
FROM BECKYWORK.LSOAHierarchy
UNION
--THIS WILL GENERATE ROWS FOR ALL Football Stadiums
SELECT
3 as Type
,'ST- ' + a.Name as Dettype
,null as Station
,a.Name as Stadium
,a.Latitude as Latitude
,a.Longitude as Longitude
,null as Year
,null as Entries_Exits
,null as Interchanges
,a.Capacity as Capacity
, b.ID as LSOAID
,null as LAD17CD
FROM BECKYWORK.STADIUMSLSOA a
INNER JOIN BECKYWORK.LSOAHierarchy b ON
a.LSOA11CD = b.LSOA11CD
So now we have a 'scaffold' to create the map - I will assign this to my model and add dimensions.
I have Joined the lowest level of my geography hierarchy (which contains the WKT polygons) to crime data. I have also joined the transformed 'scaffold' table for mapping purposes to the geography hierarchy.
My Station data has a lower level of detail than the LSOA boundaries and Football Stadiums. The station data has Year in the granularity - In otherwords, I need to interact with the icon where Entries on the tool tip are valid for a specific year. Simply filtering by station year will not work, as it will make the chloropeth and the stadiums disappear due to the fact that the YEAR field will be null for these rows.
So the best approach is to be specific on what data is viewed at each 'LAYER'. This is to create a dimension for the slicer and then add an inactive relationship. The relationship between my 'scaffold' and station year is marked as inactive.
So it will ignore my slicer until I specifically call it in a measure such as the station entries/exits measure. My new best friend for multi layer mapping is the USERELATIONSHIP DAX function
var stations = CALCULATE (MIN(MAPPINGCRIMESSTATIONS[Interchanges]),USERELATIONSHIP(MAPPINGCRIMESSTATIONS[Year],'Station Year'[Year])When I apply the station activity year, the entries value will change but all other marks within the map will remain the same due to the relationship being inactive.
I used the same method to for the choropleth measures (crimes and Stop and Search)
Activating the Street Crime Facts per measure
CALCULATE (sum(streetfacts[Crimes]),USERELATIONSHIP(streetfacts[LSOAID],'BECKYWORK LSOAHierarchy'[ID]))Activating the Stop and Search Fact table per measure
CALCULATE (SUM(StopAndSearchFacts[Searches]),USERELATIONSHIP(StopAndSearchFacts[LSOAID],'BECKYWORK LSOAHierarchy'[ID]))With this setup, we can now add dimensions to our fact tables and slice on those fact table specific attributes without it affecting the other layers.
The chloropeth polygons are dynamic - I have allowed the user to change between LSOA boundary and District boundary.
I initially figured this will be easy simply by creating a switch to toggle between the two polygon fields. However, if you remember, the icon map is set to the lowest level of detail per mark - and all the Chloropeth rows are at LSOA level. Fortunately for the polygon being district, it will apply the correct polygon because the measure refers to the FIRSTNONBLANK function - which of course is the district and there is only one District to many Lower Level polygons.
However, the complexity lies with the colouring and the tooltip. The measure will retrieve the first LSOA measure value it finds as apposed to the district measure.
I was able to solve this by creating -YES another inactive relationship!!! This time between the mapping scaffold and the Hierarchy.
The relationship we use the majority of time is between the scaffold to the LSOAHierarchy. However, when want to view districts, I want to use the many to many relationship between district and district. This is not ideal - and perhaps I may have used a bridging table, but for 'experimenting' purposes it seemed to work and perform well.
Below is the full code needed to generate the Crime values dependent on the district chosen - and will also not impact the stations or stadium icon layers.
Crimes =
var LSOABoundary = CALCULATE (sum(streetfacts[Crimes]),USERELATIONSHIP(streetfacts[LSOAID],'BECKYWORK LSOAHierarchy'[ID]))
var LAD17Boundary = CALCULATE (sum(streetfacts[Crimes]),
USERELATIONSHIP('BECKYWORK LSOAHierarchy'[LAD17CD],MAPPINGCRIMESSTATIONS[LAD17CD]),
USERELATIONSHIP(streetfacts[LSOAID],'BECKYWORK LSOAHierarchy'[ID])
)
RETURN
SWITCH ( Boundary[SELECTEDBOUNDARY],2,
LSOABoundary
,1,
LAD17Boundary
)I created a similar measure for Stop and Search
StopAndSearch =
var STOPSEARCH = sum(StopAndSearchFacts[Searches])
var LSOABoundary = CALCULATE (SUM(StopAndSearchFacts[Searches]),USERELATIONSHIP(StopAndSearchFacts[LSOAID],'BECKYWORK LSOAHierarchy'[ID]))
var LAD17Boundary = CALCULATE (sum(StopAndSearchFacts[Searches]),
USERELATIONSHIP('BECKYWORK LSOAHierarchy'[LAD17CD],MAPPINGCRIMESSTATIONS[LAD17CD]),
USERELATIONSHIP(StopAndSearchFacts[LSOAID],'BECKYWORK LSOAHierarchy'[ID]))
RETURN
SWITCH ( Boundary[SELECTEDBOUNDARY],2,
LSOABoundary
,1,
LAD17Boundary
)On the dashboard I have a toggle between Crimes and Stop and Search to dynamically change the measure values within the Chloropeth.
SWITCH Crimes StopAndSearch = SWITCH(SELECTEDVALUE('Map Metric'[MetricID]),2,Facts[StopAndSearch],1,Facts[Crimes])Icons
The Icon part of the map was easy. I created a manual table using EDIT TABLE called measure type
I then joined this onto my scaffold. I could have had an icon per station/stadium, but as I didn't have this readily available, I found 3 meaningful icons from a simple internet search and pasted them into the URL field. Of course in a real world you will probably retrieve the icons from Azure storage :)
These icons are also useful for displaying as a layers selector - I included a 'polygon' icon for this purpose, relevant for the layers selector but does not appear on the map where the real polygons will be displayed instead.
Before we can populate the map visual, we need to create the measures that will be used to render it. These will be dynamic measures which will switch between measures and mark types depending on what layer each row is referring to.
Measure for SIZE
This will apply either Chlorepeth related measures or icon related measures. You can make the size of each icon relative to the result of this measure. However, for this purpose the measure will determine whether the mark is displayed or not - no size, no mark. I have also reused this measure for the custom tooltip.
MEASURE =
var stations = CALCULATE (MIN(MAPPINGCRIMESSTATIONS[Interchanges]),USERELATIONSHIP(MAPPINGCRIMESSTATIONS[Year],'Station Year'[Year])
)
var stadiums = min(MAPPINGCRIMESSTATIONS[Capacity])
var measures = Facts[SWITCH Crimes StopAndSearch]
return
SWITCH(MIN(MAPPINGCRIMESSTATIONS[Type]),1,
stations
,3,stadiums
,2, measures
Measure for Colour
You will note with colour, I am not referring to any measure specific to stations or stadiums - keeping them at 0 will ensure that the Chloropeth is not skewed by icons with measures attached.
COLOUR =
var measures = Facts[SWITCH Crimes StopAndSearch]
return
SWITCH(MIN(MAPPINGCRIMESSTATIONS[Type]),1,
0
,3,0
,2, measures
) Measure for Object
This measure is responsible for formatting the marks on the map correctly. We are applying the dynamic boundary switch (as mentioned previously) but are also showing the right icon at the right time.
OBJECT =
var BooundarySwitch = CALCULATE (
SWITCH ([SELECTEDBOUNDARY],1,FIRSTNONBLANK('BECKYWORK LSOAHierarchy'[WKTDISTRICT],1),2,FIRSTNONBLANK('BECKYWORK LSOAHierarchy'[WKTLSOA],1))
,USERELATIONSHIP(streetfacts[LSOAID],'BECKYWORK LSOAHierarchy'[ID]))
var iconurl = FIRSTNONBLANK('Measure Type'[URL],1)
RETURN
SWITCH( FIRSTNONBLANK( MAPPINGCRIMESSTATIONS[Type],1),2,BooundarySwitch
, iconurl
)Finally, I drag the Latitude, Longitude and Dettype from the scaffold table into the Latitude, Longitude and Category data fields respectively.
Tooltip
With so much dynamic stuff appearing on the map, the tooltip needs a lot of thought and a custom tooltip was the only thing that made sense.
Below shows the same tooltip being displayed as we hover over different marks:
 |  |  |  |
Tool tip overing over a stadium | Tool tip hovering over a Train Station | Tool tip hovering over an LSOA Polygon when Crimes are displayed | Tool tip hovering over a District Polygon when Stop and Searches are Displayed |
DAX for the headings
ToolTipHeading =
var station = FIRSTNONBLANK(MAPPINGCRIMESSTATIONS[Station],1)
var stadium = FIRSTNONBLANK(MAPPINGCRIMESSTATIONS[Stadium],1)
var measures = SWITCH([SELECTEDBOUNDARY],1,FIRSTNONBLANK('BECKYWORK LSOAHierarchy'[LAD17NM],1),2,FIRSTNONBLANK('BECKYWORK LSOAHierarchy'[LSOA11NM],1))
return
SWITCH (FIRSTNONBLANK(MAPPINGCRIMESSTATIONS[Type],1),1,station,
3,stadium
,
measures
)Dax for the measure type heading
Tool Tip Measure Type =
VAR Measures = SWITCH(SELECTEDVALUE('Map Metric'[MetricID]),1,"Crimes",2,"Searches")
var icons = FIRSTNONBLANK('Measure Type'[Type],1)
return
SWITCH(FIRSTNONBLANK('Measure Type'[TypeNo],1),2, Measures,icons)I reused the MEASURE field for the values.
So here we have it - a multi dimensional mapping dashboard. Multi layered dynamicly and built from data contained within the dataset, loaded from Synapse and saved in Dual mode.
The new features of icon map really does take mapping in Power BI to another level!!! Combining the use of icon map with geospatial processing and advanced data modelling makes the number of possibilities limited to your own imagination.
I Hope you have enjoyed reading this blog. Special thanks to iconmap@jamesdales.com Icon Map (icon-map.com) who is the brains behind this amazing custom visual!