Calculate the distance between locations (with Longitude & Latitude) in Power BI

I’m working on a Power BI Dashboard that shows all the transport stops in a suburb. In this dashboard, I will need to calculate the distance between one location to others using Longitude and Latitude and I’ll share with you how to do it with DAX on Power BI. Let’s get started.

I will need to have a ‘From’ table to store suburbs' data, including Suburb Name, Suburb Postcode, Longitude and Latitude.

I will also need a ‘To’ table to store data of bus’s stations, including Station Name, Station Number, Transportation Mode (Buses/Train), Longitude and Latitude.

Let’s look at this diagram:

Just please make sure that Longitude and Latitude are set to the proper Data Category and Summarization is set as Don’t summarize.

Now on the ‘To’ table, we will get starting by creating a column to indicate our starting point.

Go to Measure tools → Select New measure

Name the measure as From, then write:

From = MAX(‘From’[PropertySuburbName]) & “to”

Then to calculate distance between one point to others, I have searched around and found this algorithm that can be converted to DAX:

Km = var Lat1 = MIN(‘From’[Latitude])

var Lon1 = MIN(‘From’[Longitude])

var Lat2 = MIN(‘To’[Latitude])

var Lon2 = Min(‘To’[Longitude])

var P = DIVIDE( PI(), 180 )

var A = 0.5 — COS((Lat2-Lat1) * p)/2 +

COS(Lat1 * p) * COS(lat2 * P) * (1-COS((Lon2- Lon1) * p))/2

var final = 12742 * ASIN((SQRT(A)))

return final

Please do not freak out. It looks complicated at first, but very easy when you get to know it. All we need to do is to put the right component into this syntax.

Lat1 and Lon1 will be the Latitude and Longitude of our Suburb.

Lat2 and Lon2 will be the Latitude and Longitude of our Transport Station.

Hence, I will have the expression as below:

We can now display everything on our Dashboard. I will have a dropdown Slicer for the Suburb so that the end-user can search for any suburb.

Then I will have a table to display The suburb (From measure), Station Name, Transport Mode and Km(Kilometres).

Finally, I will but a map to visualize all the Transport Stations. To get the map sort based on Km, also remember to put Km into the tooltips.

We will now have a complete Dashboard as above. I personally find this is quite easy after several times of using. Hope this article will be helpful for you. Good luck!