Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
DaxAmateur
Frequent Visitor

Find the nearest location for a customer

I have a table with customer names, customer location (latitude, longitude) and another list with store names and store location (latitude, longitude). For every customer I would like to get the name of the closest store and the distance to that store.

 

I have found a formula to calculate dynamic distances (from Phil Seamark) as follows:

Distance in Kilometers =
var Lat1 = MIN('From '[lat])
var Lng1 = MIN('From '[lng])

var Lat2 = MIN('To '[lat])
var Lng2 = MIN('To '[lng])
---- Algorithm here -----
var P = DIVIDE( PI(), 180 )
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

 

The approach I was thinking of would go something like....create a calculated column on the customer table that passes the customer's location to a calculation that determines the distances to each of the stores and returns the store name and distance to the closest one using the formula above.

 

Would I somehow crossjoin all the store locations with the individual customer's location and then calculate the distance?

 

Hoping this solution may be of interest to others.

 

Thanks,

DaxAmateur

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

@DaxAmateur

 

You're right - for each Customer you will have to iterate over the Stores table to find the closest one.

You can use MINX to do this iteration and return the distance to the closest store, and TOPN to return the name of the closest store.

 

I uploaded a dummy model here to illustrate.

 

Assume you have Customers and Stores tables with columns as follows:

 

  • Customers
    • Customer, Latitude, Longitude
  • Stores
    • Store, Latitude, Longitude

Then you can use the formula you've quoted in these calculated columns (I reorganised slightly so that 𝜋/180 is evaluated once per measure):

 

 

Distance to Closest Store (km) = 
VAR Lat1 = Customers[Latitude]
VAR Lng1 = Customers[Longitude]
VAR P =
    DIVIDE ( PI (), 180 )
RETURN
    MINX (
        Stores,
        VAR Lat2 = Stores[Latitude]
        VAR Lng2 = Stores[Longitude]
        //---- Algorithm here -----
        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
    )

 

Closest Store = 
VAR Lat1 = Customers[Latitude]
VAR Lng1 = Customers[Longitude]
VAR P =
    DIVIDE ( PI (), 180 )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( Stores[Store], 0 ),
        // Arbitrary tie-break
        TOPN (
            1,
            Stores,
            VAR Lat2 = Stores[Latitude]
            VAR Lng2 = Stores[Longitude]
            //---- Algorithm here -----
            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,
            ASC
        )
    )

 

These could be re-written as measures to get the closest store to any of the currently selected customers.

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

9 REPLIES 9
OwenAuger
Super User
Super User

@DaxAmateur

 

You're right - for each Customer you will have to iterate over the Stores table to find the closest one.

You can use MINX to do this iteration and return the distance to the closest store, and TOPN to return the name of the closest store.

 

I uploaded a dummy model here to illustrate.

 

Assume you have Customers and Stores tables with columns as follows:

 

  • Customers
    • Customer, Latitude, Longitude
  • Stores
    • Store, Latitude, Longitude

Then you can use the formula you've quoted in these calculated columns (I reorganised slightly so that 𝜋/180 is evaluated once per measure):

 

 

Distance to Closest Store (km) = 
VAR Lat1 = Customers[Latitude]
VAR Lng1 = Customers[Longitude]
VAR P =
    DIVIDE ( PI (), 180 )
RETURN
    MINX (
        Stores,
        VAR Lat2 = Stores[Latitude]
        VAR Lng2 = Stores[Longitude]
        //---- Algorithm here -----
        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
    )

 

Closest Store = 
VAR Lat1 = Customers[Latitude]
VAR Lng1 = Customers[Longitude]
VAR P =
    DIVIDE ( PI (), 180 )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( Stores[Store], 0 ),
        // Arbitrary tie-break
        TOPN (
            1,
            Stores,
            VAR Lat2 = Stores[Latitude]
            VAR Lng2 = Stores[Longitude]
            //---- Algorithm here -----
            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,
            ASC
        )
    )

 

These could be re-written as measures to get the closest store to any of the currently selected customers.

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi Owen, 

Many thanks for sharing this solution. It works great for finding the nearest location. 

Now I have a slightly modified problem, where I also need to find the second and third nearest locations to my list of Postcodes. 

 

I modified your formula to change "Firstnonblank" to "Lastnonblank" and set TopN to "2" and "3", respectively. However, unfortunately the TopN function doesn't always give me the right answer.

This is because when I set the order as "ASC", it does list the three closest locations to the selected Postcode. However, it does not sort those three rows in an Ascending order. So the "Lastnonblank" function returns the correct answer 1/3 of the time. 

 

Can you suggest anyway to further modify your "Closest Distance" formula so that I can get the 2nd, 3rd, etc. closest locations to my selected postcodes?

 

Cheers

The Algorithm works great! Thank you very much!

Anonymous
Not applicable

Can you give more details about the formula you use for calculation?

I have tried your code with a dataset of 250,000 rows  in table1 , and the location place table has 67,000 locations , just checking to see if it might work better as a measure , as at present it just freezes up as a column dax formula , any help would much appreciated

Thank You So Much for your dedication. 

Thank you very much.  I tweaked this a little to find the zip code instead.  works great and saved me a lot of extra work.

Anonymous
Not applicable

Can you share the zip code solution you created?

I came up with a similar solution to the topN using cross join but your solution is way more elegant and avoids the circular reference bug I encountered. Thanks for spending time on this problem - really appreciate it. Now just trying to fully understand how it works!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.