The 2nd Edition of #25daysofdaxfridays challenge is here!
How can we help?

Welcome to Curbal Learning Portal. Search for answers using the search box below,
or create a request for a video or blog if you cannot find your answer.

Popular searches: DAX Glossary | Guided courses | Request a video


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 location to based on lat/long 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
    DIVIDE ( PI (), 180 )
    MINX (                                      --calculate the MIN
        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 ) ) )

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)

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.

Map geo locations with Google Maps API when you dont have lat and long available in Power BI

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

Let us know your thoughts...

Table of Contents