Print

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:

Find closest customer/ house/ shared bike... to your salespeople/🌋/location ;) in Power BI

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:

Model of tables

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

Conditional format a Power BI map

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.

Use Google Maps API with Power BI Desktop -Download36

or learn how to push a button your report to find your closest customer/bike share…. in Power BI

Push the button to find your closest customer/bike share.... in Power BI
Was this helpful?

Reader Interactions

Leave a Reply

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

Table of Contents