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:
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.
From City = SUMMARIZECOLUMNS( 'To Cities'[country], 'To Cities'[To City], 'To Cities'[lat], 'To Cities'[lng] )
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.
Countries = SUMMARIZECOLUMNS('To Cities'[country])
I then created relationships between the three tables as follows:
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’
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 :
Kilometers = var Lat1 = MIN('From City'[lat]) var Lng1 = MIN('From City'[lng]) var Lat2 = MIN('To Cities'[lat]) var Lng2 = MIN('To Cities'[lng]) ---- Algorithm here ----- var P = DIVIDE( PI(), 180 ) var A = 0.5 - COS((Lat2-Lat1) * p)/2 + COS(Lat1 * p) * COS(lat2 * P) * (1-COS((Lng2- Lng1) * p))/2 var final = 12742 * ASIN((SQRT(A))) return final
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.
For the Map visual I configured as follows, again using the Top N visual level filter to help find the closest 10 cities.
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.
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
Please feel free to comment or feedback so I can improve for future blog posts.
8 thoughts on “Dynamic distances in Power BI”
Neato. Would be cool to add some kind of parametization to that, so you could get it to return x largest cities with popn above y.
Is it possible to implement in Excel 2013 power view ?
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?
Do you mind sharing how you were able to select a filter and map, that would allow me to select a postcal and a mile radius to display all of the items within that radius.
Thanks, rather new to geospatical dax forumals, and this article was extremely useful, albeit
for a slightly different use case (determining the distance of suppliers from the central point of Suburb), but worked a treat.
I am trying to tweak it slightly and struggling though, to count the number of suppliers within X km of Suburb… using the suburb as the dynamic filter.
Not sure if you are able to help, though trying to figure out a suitable dax measure to calculate the number of suppliers within Xkm of suburb location for each suburb.
Fantastic article. It helped me immensely. Keep up the cool Power BI stuff!
Hello, I came across this article while trying to research an idea to analyze order transaction information and distances. Super cool article. I am trying to put it to work for my use case, but I’m struggling to figure out how to create the proper relationship between the existing address information and the city tables with lat/long values. Overall, I am trying to evaluate how many transactions take place as distance grows between the address provided on a transaction and a pick up location. My current data model includes transaction details which has full address (city/state/zip) and pick up store location number which is linked to a store location table to identify where the pick up location is (city/State). Do I need to add lat/long info to my store location table? Or should I use the unique city ID that’s on the lat/long table above and add that to my store location table? Any help would be greatly appreciated!
Very helpful. Appreciated work.
Thank you for sharing this article with us.