Dynamic distances in Power BI

Posted by on Jun 3, 2017 in DAX, Power BI | One Comment
Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

I recently encountered a question posed to the Power BI community forum I found interesting enough to kick-start my blogging on Power BI.

The essence of the question was asking how to dynamically determine distances between two geographic points from user based selections.  So I thought I would cover how this can be done in Power BI to show the closest N cities based on straight line distance.

Firstly, I downloaded a list of cities from http://simplemaps.com/data/world-cities.  The free file contained approximately 7000 cities with lat/long info.  The data also carried country and province info which may be useful for further analytics.

The data-set looked like this:

dataset

I was primarily interested in the city, country, lat & lng columns.

After importing to Power BI I renamed the table ‘To Cities‘ and then made two calculated tables based on this.

The first table is to generate a list of cities and countries to use as my origin or from table.  I named this table ‘From City‘ and used the following DAX to create the table.  This is the table I would use to allow users to select an origin.

The second table was simpler and only contains a list of countries.  I would use this in a country slicer to filter down to both the From and To city tables.

I then created relationships between the three tables as follows:

B1 relationships

Both city tables are connected to the Countries table, but importantly there is no relationship between ‘To Cities‘ and ‘From City‘ tables.  This is because I do not want either table to act as a filter on the other.

For each Lat/Lng field I made sure I set the Data Category to an appropriate setting as well as specified that the default summarization should be ‘Don’t Summarize’

B1 Categorisation

Now I had my data and my relationships in place, I needed a calculated measure to dynamically derive the distance between cities.  I did some internet searching for an algorithm to determine the distance between two pairs of lat/long points. I found a few versions but not all could be easily converted to DAX using the existing math functions.

Fortunately I found one which I used in the following calculated measure :

I won’t pretend to follow the maths, but I did find the same formula repeated in different places and after a little reconciliation using Google Earth and its distance measurement tool I was happy it was good.

The result is kilometers but can be easily tweaked to return the distance in miles.

With the measure added to my ‘To Cities‘ table, I could build a simple report page.

I added two slicers to allow end users to filter on Country and From City which determine the origin city to be used in the [Kilometers] calculated measure from above.

Then I added a matrix and map visual to the report canvas.

The Matrix visual I configured as follows.  The main point here is that the ‘To City’ column is using a Top N filter which I set to use the bottom 10 values based on the kilometers measure.  I used Bottom so that it would filter the closest cities.

B1 Matrix

For the Map visual I configured as follows, again using the Top N visual level filter to help find the closest 10 cities.

B1 map

My very basic report finished up like this, but allows me to choose a country and city from the two drop-down slicers and the matrix and map would show me the closest 10 cities.  The matrix is sorted using the Kilometers column and I also added the distance measure to the tool tip on the map.

B1 finshed report

This approach can easily be tweaked to allow for a number of use cases and I hope my blog debut proves to be of use in some way.

Here is a link to the PBIX file.  The model itself is very plain but hopefully useful

https://1drv.ms/u/s!AtDlC2rep7a-kBygBveM-q8V8fh-

Please feel free to comment or feedback so I can improve for future blog posts.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Philip Seamark
Consultant at RADACAD

Phil is Microsoft Data Platform MVP and an experienced database and business intelligence (BI) professional with a deep knowledge of the Microsoft B.I. stack along with extensive knowledge of data warehouse (DW) methodologies and enterprise data modelling. He has 25+ years experience in this field and an active member of Power BI community.


One Comment

  • Hi,
    The above article was very useful in plotting my requirements. However their is one more query which I am facing. How should I add a chiclet or something which gives me control to select assets within a specific mile radius. I am able to achieve this via filter panel but want this to be on my report page itself. Any ideas?

Leave a Reply

Your email address will not be published. Required fields are marked *