Nearest location based on Lat/Long in Power BI
In this post, I am going to show you how to find the nearest location/s based on a specific location.
If you prefer to watch in video form, click below:
1. How to apply the Haversine formula
To calculate the distance between two points on earth we need to use the Haversine formula:
a = sin²(ΔlatDifference/2) + cos(lat1).cos(lt2).sin²(ΔlonDifference/2) c = 2.atan2(√a, √(1−a)) d = R.c
For our example (Austin Bike sharing service from Google Public Datasets), we created a table with the reference point where we want to measure the distance from and loaded it into Power BI without adding any relationship to it:
And we created the following DAX measure:
Closest Distance = VAR Lat1 = SELECTEDVALUE('My location'[latitude]) ---my latitud VAR Lng1 = SELECTEDVALUE('My location'[longitude]) --my longitud VAR P = DIVIDE ( PI (), 180 ) RETURN MINX ( --calculate the MIN bikeshare_stations, VAR Lat2 = bikeshare_stations[latitude] --the station lat VAR Lng2 = bikeshare_stations[longitude] --the station long //---- Haversine ----- 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 )
2. Conditional format the results on the map
Now, the only thing we have left, it to conditional format the map as follows:
1. My location: Green
2. Closest locations: Blue
3. Rest of the points: Grey
To conditional format the locations, we are going to first rank them (learn the RANX function here) and then assign a color to each rank.
And the DAX code used is:
Color station and my location = VAR RankPosition = RANKX(ALLSELECTED(bikeshare_stations), [Closest Distance], [Closest Distance],asc) return SWITCH(TRUE(), RankPosition=1, "green", RankPosition<5, "blue", "grey")
And it is that easy!!
3. Additional learning resources
Want to learn more about measuring distances in Power BI? Check this video too on how to use Google Maps API in Power BI to map locations when you don’t have lat and long.