Inset Maps with Power BI Shape Maps

Inset maps – sometimes known as locator maps –  are small maps that sit alongside a main map and act provide context for the main map.  They are often used for example when a main map shows a close-up of a region, to show where that region sits within a larger area such as a county.

We can create Choropleth maps in Power BI which show zoomed in areas, with an inset map to place the main map in its wider context.  This blog post describes how I do it.

Choropleth maps or shape maps in Power BI use custom topojson maps files and I’m going to use two topojson files that I’ve already prepared for this example.  See this post on how to create topojson files for creating choropleth maps with the Shape map visual.

The dataset that this example is using is the police street crimes data set available from I am using a sub-set of the data for the Lancashire Police Force Area. The main map displays the street crime totals by LSOA.  In order to support basic exploration of the data I have enhanced the data model with a geography dimension or lookup that places each LSOA area within its respective Local Authority.  This enables filtering of the data by local authority to show only those LSOAs within the selected Local Authority.  To display the map on the report  I am using the Shape map visual.  In its default view, the map shows all the LSOAs in Lancashire as seen below.

While this is effective in the county context as it shows the whole area in  a single view with greater crime in the darker shades, some of the detail is lost because of the scale of the map.  It would be useful to show the data for each local authority in more detail and there are a couple of options to do this; one would be to create a map for each local authority, but this is time-consuming and not easy to display in Power BI (unless each Local Authority had its own report page for example); another option is to enable auto-zoom on the Shape map, so that when a filter on Local Authority is applied, the map zooms to the extent of the selected area.  This works nicely, but has the side effect of isolating the Local Authority from the wider county/police force area.

This is where the inset map comes in. By placing a second smaller map close to the main map, which shows all the local authorities in the Lancashire police force area, it is easy to relate the zoomed in extent of the main map to its highlighted counterpart in the inset map.   Furthermore, due to the cross-filtering support in Power BI the inset map can be used to drive the selection of the Local Authority and zoom in the main map. Nice!

This is how the main map looks zoomed in on Preston with the Preston local authority highlighted in the inset map.


Step by step guide

These are the steps required to create the above map

The following are required:

  • A data set containing the crime data for Lancashire
  • A geography dimension to allow grouping and filtering of the data into different hierarchical areas.  The data already includes the LSOA area, so we can use this to relate the crime data to the geography dimension which includes the hierarchy of areas such as LSOA – Local  Authority – Police Force Area.
  • A topojson map file  for the all the LSOAs in the Police Force Area (if you don’t know what this means, read this!)
  • A topojson map file for the Local Authorities in the Police Force Areas to use as the Inset Map.

You will have all the above pre-requisites if you read this post first

I’m assuming the Power BI file exists with the data loaded.  The rest of this post steps through the map creation.

On a new report page, add a Shape map. (If the Shape map visual is not available in the visuals pane, you may need to enable it from from Preview Features.  Click File – Options and Settings – Options, then select Preview Features and tick Shape map visual).

To the Location field property add the LSOA Code and to the Color saturation field property add the Total Crime measure (this is just a simple count of rows).  The default map of North America will display.  To change this to our LSOA map, click Format (paint roller) and click Shape to open up the Shape field well.  Click Add map, and select the LancashirePoliceForceAreaLSOAs.json file.

You should see the map display change to show the correct map.  You can style the map however you wish, but to follow the example I’ve used, open up the Data colors field well, enabled diverging, and set the Maximum, Center, and Minimum colours to use the Orange colour range where Maximum is the darkest shade, Center is the middle shade and Minimum is the lightest shade of orange.

You will notice that the map appears to be mostly a single shade of orange despite setting the colour range.  This is because the dataset is as yet un-filtered and there are some LSOAS in the country with some very crime totals.  To fix this add a filter to the filter pane for Police Force Area, which is in the geography dataset,  and filter on Lancashire.  You will notice a slight change to the shading on the map, as the map is setting its colour range over the range of crime totals only across Lancashire.  However its still not ideal.  This is because there are a few crime hotspots with very high crime totals. i.e. the data is not evenly distributed across the LSOAs.  You can see this if you add a bar chart to the page with LSOA Name on the axis and total crime for the value.  The vast majority of LSOAs have less than 50 reported crimes, but there are a few with many more with the max being 233.  So instead of allowing the visual to automatically scale colour range, we can manually set these values. (Be aware though that manual values may not accurately reflect the distribution of any new data that is added when the data is refreshed, so manual configuration would need monitoring in production).

For this example, I’ve set the maximum value as 100 (so any LSOAs with 100 or more crimes will be shaded in the darkest colour), and the Center colour as 20. You can experiment with these to see what works best for your data.  In general, if the data is normally distributed, then the defaults may work, but if your data is skewed as in this case, setting manual values is likely to work better, subject to the caveat about refreshed data.

In the Shape map Default color field well, change the background colour to white and the border colour to a pale grey (these settings help to make the inset map stand out when the main map is zoomed).

In the Zoom field well turn on Auto-zoom

Finally turn off the visual header.

Add the inset map

The procedure to add the inset map is similar to the main map.  Add a Shape map visual to the report but size it appropriately and position it to the top right of the main map (You can adjust these later, but get the general size and location approximately correct now).

To the Location field add Local Authority from the geography dataset.  We don’t need to add a value as we are not intending to use this map to aggregate and data.

From the Shape field well, add a custom map.  This time add the LancashirePoliceForceAreaLocalAuthorities.json file.

To style the inset map, set the data colour to something subdued, such as a mid-grey so as not to detract from the main map. Also set the default colour to light grey and the border to white. Add a border ( I like a grey border with a radius of 15), and turn off the visual header

Set the background to on, and set the transparency to about 30%.  This ensures that the inset map remains clearly visible but does not completely obscure any parts of the main map behind it (which can vary depending on what is selected).

Turn the title off (for one or both maps if desired), or set to something appropriate.  I prefer the inset map to have no title as its purpose should be obvious.

Some adjustment to position and size of both maps may be required, but the inset map can now be clicked to filter and zoom the main map while providing that all important higher level context.

Heres the finished report.  Click the bars or the inset map to see it in action! (Best viewed fullscreen)

Choropleth Map

Creating topojson map files for Power BI Shape Maps with

Shape maps or Choropleth maps are a great way to visualise spatial information where the data needs to represented in some predefined area such as a country, county, or smaller area.  Choropleth maps are especially useful for comparing differences between areas, and representing the data as a ratio of some other variable, such as the crime rate per 1000 people, or population density per square kilometre.  Power BI support Choropleth maps through several visuals including the Shape map, drill-down choropleth and Mapbox visual.  The first of these is currently in preview, and the latter two are custom visuals so don't appear in the visuals palette by default.

The default maps in Power BI - the map and filled map visuals - are easy to use, providing your data has location attributes, such as latitude and longitude and offer some customisations that may meet your needs.  As an example see this post where I look at new heat map feature in the map visual.  The filled map in particular does allow the display of choropleths but it is limited to displaying a default set of areas that it knows about.  So data aggregated at a country level is likley to display as expected, but at different geographies the results may not be as expected.  In the UK, the filled map can make a reasonable (but not perfect) attempt at mapping county level data, but at most other geographical levels, such as region, health authority, police force authority, or smaller units such as postcode, ward or cenus output area the filled map does not work.  The filled map relies on Bing for its "location intelligence" and where Bing Maps and Search cannot resolve a specific geography, the filled map fails.  For example. it is not possible to use the filled map to display data aggregated a Local Authority level in the UK and this is a requirement that I frequently see, especially in the public sector.

The solution to this problem is to use a visual that fully supports Choropleth maps and allows you to specify exactly what areas you wish to to see mapped.  However, to do this you must source appropriate base maps files, convert themt to a supported format for Power BI and prepare your data to take advantage of the custom maps that you have prepared.

I am going to go through an example using the Shape map visual which is currently in preview, so to use it, it must first be enabled in Power BI through Options and Settings - Options - Preview Features. We will need some data to map, so I'm using a dataset of street crime in Lancashire that I've already prepared. We will also need the map files to provide to the shape map visual, and that's what the rest of the post is about.

Sourcing map files

First we need to get hold of the map files. Where to get source maps files depends on which area you want to map, but in the UK we are fortunate to have an abundance of open mapping data provided by the Office of National Statistics and accessed through the Open Geography Portal

I've downloaded the following map files:

Police Force Areas

Local Authority Districts

Lower Layer Super Output Areas

I need the Police force areas map so that I can extract the Lancashire polygon.  Although I won't be using this for visualisation, it will be useful to use as a selection mask for the the Local Authoritiesa and Lower Layer Super Output Areas (LSOA) map. The Local Authorities Districts maps will allow data aggregated at the Local Authority level to be displayed.  The LSOA map will allow data aggregated at the LSOA level to be mapped.  I chose these because each crime observation in the data set contains location data on where the crime was committed.  One of the columns is LSOA.  There are also columns for latitude and longitude which is helpful should I wish to aggregrate by some other level of geography, but in this case LSOA is perfect.

It's worth noting that there is a choice of files to download. The area mapped is the same, but the file sizes are different, depending on whether the polygons have been generalised - which essentially means reducing the number of points describing the polygons in the map.  This saves space, but at higher magnfications the resolution of the generalised and ultra-generalised maps is too coarse.  The generalised files are fine, but in this case I've downloaded the full extent (full resolution) files, because I need to do some processing on the files to get the final maps I need, and I have had issues with this using the generalised files. It is possible to generalise or simplify the maps yourself anyway to reduce the file sizse as you will see.

Once downloaded, the files shoud be unzipped.  The files are ESRI shp format which is a widely accepted standard for map files, but not compatible with the Shape map visual in Power BI.  We will need first extract the polygons - for Lancashire - from the the files and convert them to topojson format.

There are various freely available tools to manipulate map files such as QGIS, ogr2ogr, R and others, however all these require installation. is a free online resource that can we can use for map preparation.  Here is a step by step guide.

Open in a brower.

Select, or drag and drop the unzipped police force area files to the mapshaper window.  There are 6 files in the extract but only the shp, shx, prj and dbf files are required.  If you select all six mapshaper just loads the four it needs.

Click Import and you should see a map of English and Welsh police force areas.  We are only interested in Lancashire so click the Console link and in the console type the following:

filter 'pfa17nm == "Lancashire"'

This removes all polygons except Lancashire from the map. Click the filename in the mapshaper header bar and click on the name to select it.  Change the name to LancashirePFA.

We will use this as a template to filter just the Lancashire local authorities from the local authorities map.

Click "Add a file" and import the Local Authorities files following the same procedure as for the police force areas map.

In the console window type clip LancashirePFA.  This will remove all the Local Authorities from the map that are not masked by the LancashirePFA map or layer.

Click the filename in the mapshaper header to open the layers window.  Rename the layer to LancashireLocalAuthorities.  You can also delete the LancashirePFA layer since we have finished with it.

The map must be in the wgs84 projection so in the console type

proj wgs84

(not WGS84 as it is case sensitive)

Click Export, select topojson as the format, and click export.  Save the file with the name appropraitely, e.g. LocalAuthoritiesInLancashirePoliceForceArea.json

You now have a topojson file that can be used with the shape map visual in Power BI to map data aggregated at the local authority level.

To map data at the LSOA level, you should repeat the steps above with the LSOA map file that you downloaded.  Note that you can use the Local Authorities map as the clipping layer in mapshaper to filter only those LSOA that fall within those local authorities (and by default within the Lancashire police force area).

The rest of this post just considers the Local Authority map.

In Power BI add a Shape map to a page.  In the location field add Local Authority. In the value field add Total crime. Click the paint roller, then click shape. CLick Add map.  Add the json local authority map file that you created in Mapshaper.  You should see your map filled according to the data values for each Local Authority.  You can now modify the data colours to suit and add additional visuals to build out the report page.  The map fully support cross filtering so you can click on Local Authorities in the map to filter other visuals or highlight local authorities in the map through filtering other visuals.

The following report was built using the Local Authority map file created above.

The Power BI file for this report can be downloaded from here.


Heat maps in Power BI

As of the March 2019 update, Power BI includes a heat map option available through the existing map visual.  It works well and is a welcome addition to the built in map features of Power BI, but there are a few issues to consider. There is a also a custom visual heatmap published by a Microsoft employee on AppSource. that may fit your needs better.  Read on for more details…

The March 19 release of Power BI has introduced heat maps for displaying spatial point data.  It’s nice to see the mapping capabilities getting some attention at last as this as one area of huge untapped potential in Power BI.  To a large extent, we’ve been left with the default bubble and filled maps with the shape map still stuck in preview for well over a year!  For anything beyond basic mapping, especially where you need some spatial intelligence you’re really looking at third-party offerings from MapBox and Esri. It really doesn’t seem like mapping is a high priority for Power BI.  However, in October 2018 Tableau released heat maps, and where Tableau goes, expect Power BI to not be far behind.  (The Tableau heat map feature appears to have broader application than maps though, being a “density mark type” that can be applied to other visual types such as scatter plots too.)  So now we have heat maps in Power BI!

As part of this review I also looked at what alternative heat map custom visuals are available via Appsource.  There is currently a visual available for free called Heatmap created by a developer named Weiwei Cui.  A little bit of investigation reveals that Weiwei works for Microsoft Research Asia, so although this is a “third-party” custom visual, that third-party is in effect Microsoft.  I say this because, I and many other developers are cautious about using custom visuals in production because of ongoing support and maintenance implications.

Previously the built-in mapping options for point data (i.e. a single data point with a latitude and longitude) were limited to the bubble map and it is this visual that has been updated with a heat map option.  The other built in map – filled map – also takes point data which includes latitudes and longitudes but fills or shades an area of the map according to some existing boundary, so the data is aggregated to the area defined rather than the precise area on the map with the bubble map.

You may be familiar with the appearance you get when you add some spatial data to a bubble map in Power BI. The initial affect is rarely what you want especially if you have a lot of data points.  You tend to get a dense cluster of points that completely obliterate the underlying layers of the map. You can tweak the bubble size and change the colour, but that’s about it when it comes to customisation.  For this reason its a map type that I’ve infrequently used but with the heat map capability it’s time to look at this visual again.

For this review I downloaded the street crime data for Lancashire from  This is a nice open data set for this kind of demonstration with each recorded crime including its latitude and longitude as well as some other interesting attributes.  I’ve included data from from February 2016 to January 2019 in the dataset and as you can see below this results in the usual blob of data points obscuring the underlying map.  We can do better with a heat map!


Here are my thought and findings:

The heat map feature is straightforward to use and it is built-in to the existing map visual.  You can now choose the default bubble map or turn the heat map setting on to convert the map to a heat map and enable a different set of customisations relevant to this map style. It can take some time to tweak the data point radius and colour scheme to get the effect that you want and there are a few things to be aware of.

The radius of the data points can be set in pixels or metres.  This has implications when zooming in or out.  As you zoom in, you might expect the areas of highest intensity (most data points) to become more prominent or easier to see, but this doesn’t happen if the radius is set in pixels.  In fact the opposite occurs as the data point density is less dense as it is spread over a greater part of the screen.  You can alter this behaviour by setting the radius in metres.  This fixes the area of intensity so that zooming retains the “hotspots”.  However, if you zoom out far enough, the pixel radius performs better as the metres radius shrinks until it becomes practically invisible.  This matters because your map may auto-zoom depending on what cross-filtering you have enabled, so you need to understand the characteristics of the data – i.e. how spread out it is on the map, to determine how to specify the radius.

The custom Heatmap handles zooming differently.  As you zoom out the datapoints become relatively closer together so the heat intensifies. As you zoom in the opposite happens.  But, instead of the heat spreading (and the colours at the low end of the colour scale predominating, each point retains its intensity.  In effect, on the new heat map, when the zoom is high enough that each point is identifiable, its colour is at the low end of the range (e.g green).  This can make the patterns in the data difficult to discern when zooming in.  The custom heatmap avoids this by revealing each data-point as a dot using the colour from the high end of the range.  The behaviour of the custom visual just feels more intuitive.  However, there is one issue to bear in mind in that since heat maps display a higher  density of data in a more intense colour, you may not want a single point when zoomed-in to show in high intensity (e.g. Red), unless of course there are multiple data points at that precise location.  So in that regard the new heat map handles this better - despite the effect on how clear the data points are when displayed in low intensity colours (e.g. pale green).  I didn't find any way to change the default behaviour of the custom Heatmap to behave like the new heat map.  At least you have a choice between the two visuals depending on what you want to see when zoomed-in but its a pity you can't toggle this behaviour in  a single visual.

Try zooming in and out on the maps to see this effect.

The bottom line here, is that the scale / zoom level has an impact on the heat map display.  Some experimentation will be needed with zoom levels (and radius settings with new heat map) to get the desired effect. Disabling auto-zoom might help maintain the default zoom level, but the user could manually zoom anyway and disabling auto-zoom would stop the map zooming in to the current bounds of the data when slicers or filters are applied, which is probably not what you want to happen.

The bubble map has also been included for comparison.  If your only choice is between the original bubble map view or the new heat map view, your choice would be determined by how many data points there are on the map and what level of scale or zoom you need the map to be at.  The bubble map woud be more effective when zooming in to few data points as they would stand out better on the map compared to the heat map.  For a higher-level view with more data points, the heat map gives a better representation as the overlapping datapoints are turned to advantage in the heat map to display the density of datapoints.  This overcomes the major drawback of the bubble map of overlapping points obscuring underlying pattern in the data-  how do you know how many datapoints lie under the pile you can see? Of course you can reduce the size of the bubbles as I have done in the example, but the whole point of the heat map is to show dense data that would cause even very small points to overlap.  The custom heatmap has the best of both worlds as it deals with the heatmap representation of the data but reveals more clearly identifiable points when zoomed. (N.B. A single point on the the map is not necessarily only one data point though, as points at the same precise location are aggregated).

The heat map suffers from the major shortcoming of all the Power BI maps.  No map legend or distance scale!  These are such fundamental map basics, their omission is mystifying.  The custom heatmap visual has both a legend for the data and a distance scale.

One area where the new heat map is better than the bubble map and custom Heatmap visual is in map labels.  The new heat map puts labels above the data on the map, so the colours do not obscure placenames.  On both the bubble map and custom Heatmap, labels are obscured.  This can be mitigated somewhat by setting the transparency of the data colour, but the new heat map approach is better. The best option of all would be to have a configurable setting to specify the z-order of the data layer and map labels, but none of these maps provide this.

There appears to be a bug in the new heat map where the data becomes invisible. It seems to happen intermitently, e.g. when switching pages, but is difficult to reproduce and doesn't seem to be an issue on published reports, so might only be an issue in Power BI Desktop.

There’s an irritating behaviour in the radius settings. If you delete the current value, you must enter the new value immediately (i.e. within about 250ms) or the default value suddenly appears.  I found I often ended up with a concatenation of the value I typed followed by the default value when I was experimenting with this setting.

The custom Heatmap is far more customisable.  There are five levels for the colour range, whereas the heat map has three.  The base map in the custom Heatmap has the option to turn on or off the display certain map features such as labels, place names, roads, etc.  It also supports multiple languages.

The custom Heatmap also has an additional rendering style called contour, which is effectively an isoline (e.g. isobar, isotherm) map.  This effectively changes it from a heatmap, but is an interesting option to consider.

I experienced an issue with the custom Heatmap once deployed to the Power BI service in that it appears to show nothing other than the base map when on a page with the other maps. Selecting a filter though – or just clicking on one of the other visuals-  does show data.  The map on its own page did not have this issue.

In summary, if you need heat map functionality from one of the Power BI built in maps, can live with the limitations and can't use a custom visuals then now you have it.  But Weiwei Cui’s Heatmap offer a bit more customisation and may fit your needs better.  Either way the addition of the new heat map feature to the map visual is a welcome improvement.  I'm looking forward to more map features in future Power BI releases.

Foreign key constraints in data warehouses

I’ve had a recent engagement troubleshooting some data warehouse/ETL issues, one of which was some broken referential integrity (RI) between a fact table and its dimensions. This made me think about a recurrent issue in data warehouse design which is should you use foreign key constraints. So, here’s my current thinking/practice on the issue for my and others reference.

Every fact table has foreign keys to the dimensions that it uses/references. The relationship is one-to-many with the dimension on the one side and the fact on the many. There cannot be an id for a dimension row in the fact table that does not exist in the dimension itself – that would break referential integrity and result in spurious results in any reports. That’s not to say that a fact can’t exist if the dimension row is still unknown or not yet loaded – there are techniques to handle late arriving dimension rows – but whatever value exists for the fact table’s foreign key for the dimension in question must exist. If it doesn’t something is badly wrong.

The easy way to guarantee that this never happens is to create a foreign key constraint between the dimension and the fact. When this is enforced it is not possible to insert (or update) a fact with an invalid or missing dimension key. So, this is the simple solution. However, there is a cost to maintaining referential integrity and one of the goals of data warehouses is performance. Now the existence of foreign key constraints won’t have any impact on report performance, but it may impact ETL. If there are issues with ETL performance, such as a time-consuming load process or a very small ETL window before up-to-date reports must be available, then optimising the ETL process may be required. When the fact table rows are inserted, the existence of foreign key constraint will impact the time taken to load the fact table, because after all, this constraint requires referential integrity is checked at insertion time. No related dimension record will cause the integrity check to fail and the fact won’t get inserted. This would normally be a good thing, except where you have to squeeze the last bit of performance out of your ETL process, in which case disabling the constraint may help.

However, once this is done, the door is now potentially open for compromised referential integrity. There is an argument that a well designed and tested ETL process should look after referential integrity and you should never see a fact with an invalid/missing foreign key. While this is true, things never stay the same and there are always future unknown unknowns such as inexperienced developers working on the ETL and breaking something.

Often, when I am faced with decisions such as this I refer to Kimball. This is what The Data Warehouse ETL Toolkit (pp 212-214) says on the matter:

Option 1, check RI during data preparation in the ETL process

Option 2, do it at insert time – i.e. use foreign key constraints

Option 3, create a process to check RI after the fact has been inserted.

Kimball says option 1 is often the most practical, because of the performance hit of option 2, but points out that this is just a software/hardware limitation in practice and may not be an issue with a particular system or environment. Option 3 is generally not preferred to the other two options.

So Kimball seems to lean towards checking RI in the ETL over foreign key constraints. However, I subscribe to the principle that simple is better than complex, and ETL processes can get quite complex, which means that during further development/maintenance subtle errors may creep in. Foreign key constraints are by contrast simple to understand and simple to implement.

So, without getting too deep into a theoretical and subjective argument here is my current practice for foreign keys constraints:

  • At design time foreign key constraints should be used. They guarantee referential integrity when the facts are inserted and will throw an error that the developer/admin can catch and fix before the users report issues with the data.
  • Design the ETL as if the constraints are not there; i.e. follow Kimball’s recommendation to enforce RI during data preparation.
  • If ETL performance is an issue and removing the foreign key constraints will make a difference then remove the constraints. But, test the pre and post ETL performance to see what’s been gained, and only do this if it makes real difference and other performance optimisations haven’t been tried first or have been ruled out for other reasons, such as cost.

This gives the assurance that RI cannot be compromised, even if modifications are made to a complex ETL process, while at the same time providing scope to optimise fact table load speed by disabling the foreign key constraints should it be required.

Power BI at Powys County Council

I recently completed my first engagement as an independent consultant.  The project was to design and build a comprehensive set of Power BI dashboards to showcase the various datasets that contribute to , or impact the well-being of the people of Powys.

I worked with the Business Intelligence team in Powys between April and July this year. The project was completed on time which was helped due to the adoption of Agile working practices by the team in Powys.  The datasets were comprised mainly of internal data sources and public data published by the Welsh government on its data portal

Statwales publishes many of its datasets to the Odata specification, which makes consuming the data in Power BI projects very straightforward.  And Power BI makes it an awful lot easier than consuming the same data via SSIS in a traditional BI project!

Many of the dashboards and reports have a spatial element.  I spent sometime reviewing the mapping capabilities of various mapping visuals in Power BI and have a separate blog post (in prep) on mapping in Power BI.

The map visual used mostly throughout the project was the shapefile custom visual, which is still in Preview.  However, it proved to be perfectly stable for use in this project.

I worked with a very talented data-visualisation expert from the Powys BI team who added some much needed style and polish to the finished reports and while I am extremely pleased with the finished versions of the reports I can only claim partial responsibilty for this!

Here is a sample of the work below and there’s many more on the Powys Wellbeing Information Bank Portal.  If you need help with Power BI or other data related projects, please get in touch!