Find the nearest store and distance

[theoandino]

I have a table excel (kode store, name store, latitude, longitude) with a total of 25 outlet. I want to make it efficient to merge several stores considering the closest distance.

How can I find the nearest stores and the nearest stores distance (excluded the store itself because it will return a value of 0.0 km)

I use some formula but return a value of 0.0 km because the store itself

CLOSEST_STORE =

VAR Lat1 = outlet_distance[LATITUDE]

VAR Lng1 = outlet_distance[LONGITUDE]

VAR P =

DIVIDE ( PI (), 180 )

RETURN

CALCULATE(

FIRSTNONBLANK ( outlet_distance[STORE_NAME], 0 ),

// Arbitrary tie-break

TOPN (

1,

outlet_distance,

VAR Lat2 = outlet_distance[LATITUDE]

VAR Lng2 = outlet_distance[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

)

)

DISTANCE_TO_CLOSTEST_STORE_(KM) =

VAR Lat1 = outlet_distance[LATITUDE]

VAR Lng1 = outlet_distance[LONGITUDE]

VAR P =

DIVIDE ( PI (), 180 )

RETURN

MINX (

outlet_distance,

VAR Lat2 = outlet_distance[LATITUDE]

VAR Lng2 = outlet_distance[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

)

For example closestest store upc ahmad yani is upc mall cipto about 1 kilometer

data pbix here : http://bit.ly/distance_powerbi

Is there anyone who can help me ..

Hoping this solution may be of interest to others.

@OwenAuger 

Thanks,

Theo

[AlB · Accepted solution]

Hi @theoandino 

For the distance you just need to exclude the current store from the search (look at the FILTER() as base table for the MINX()):

DISTANCE_TO_CLOSTEST_STORE_(KM) =
VAR Lat1 = outlet_distance[LATITUDE]
VAR Lng1 = outlet_distance[LONGITUDE]
VAR name_ = outlet_distance[STORE_NAME]
VAR P =
    DIVIDE ( PI (), 180 )
RETURN
    MINX (
        FILTER ( outlet_distance, outlet_distance[STORE_NAME] <> name_ ),
        VAR Lat2 = outlet_distance[LATITUDE]
        VAR Lng2 = outlet_distance[LONGITUDE]
        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
    )

and for the name of the store:

CLOSEST_STORE =
VAR Lat1 = outlet_distance[LATITUDE]
VAR Lng1 = outlet_distance[LONGITUDE]
VAR name_ = outlet_distance[STORE_NAME]
VAR P =
    DIVIDE ( PI (), 180 )
VAR auxT_ =
    ADDCOLUMNS (
        FILTER ( outlet_distance, outlet_distance[STORE_NAME] <> name_ ),
        "Distance_",
            VAR Lat2 = outlet_distance[LATITUDE]
            VAR Lng2 = outlet_distance[LONGITUDE]
            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
    )
VAR minVal_ =
    MINX ( auxT_, [Distance_] )
RETURN
    MINX ( FILTER ( auxT_, [Distance_] = minVal_ ), outlet_distance[STORE_NAME] )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

I know this is a lot later, but do you know how to add in a check within this?  Specifically, I need to pull the closest store that sells an item if sold out at the current.

[theoandino]

Thanks for the answer......