Find the nearest location for a customer

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.