Find the nearest location for a customer

[DaxAmateur]

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

[OwenAuger · Accepted solution]

@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.

[erdcan]

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

[PA_2022]

The Algorithm works great! Thank you very much!

[Anonymous]

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

[Pandadev]

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

[MAAbdullah47]

Thank You So Much for your dedication. 

[supporterer]

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.

[aldovivo]

Can you share the zip code solution you created?

[DaxAmateur]

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!