Curbal has its own social media server on Mastodon. Check it out here!
Welcome to our Knowledge Base

Tip: Start typing in the input box for immediate search results.

# 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!! 