Distance Calculation with filters

[Anonymous]

hello, everyone, 

I have been using this formula to calculate the closest stores to my customers. I found it online on the forum and it's great. 

However, I would like to add a small nuance to it: I would like to find the closes store to my customer BASED on another column first and then do the calculation. Basically I need this awesome formula to filer the stores table by the type of customer before it calculates the closest store. 

I tried modifying the formula below but I am obtaining blank values for some. Can you guys help? Where to put the filter function so it can filter on the "Group1" or "Group2" before it can calculate the closest store to that customer?

Original Formula:
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
        )
    )

Closest Store2 = 
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
        ),FILTER('Stores', 'Stores'[Column 2] = EARLIER('Customers'[Column 2])
    ))

Here are the two tables in question:

Customer	Latitude	Longitude	Closest Store (Original Formula)	Distance to Closest Store (km)	Closest Store2 (New formula)	Column 2
Customer 1	-36.845538	174.760461	Soul Bar and Bistro	0.376745939876671		Group1
Customer 2	-36.825887	174.748118	The Hilton	2.21773122504468		Group1
Customer 3	-36.850022	174.765985	McDonald's	0.0625924022793106	McDonald's	Group1
Customer 4	-36.855739	174.766955	The Langham	0.320886788770884	The Langham	Group2
Customer 5	-36.858394	174.760322	The Langham	0.349053269527966	The Langham	Group2
Customer 6	-36.844891	174.773084	PWC	0.717601194877626		Group2

Store	Latitude	Longitude	Column 2
PWC	-36.843158	174.765316	Group1
ASB	-36.851273	174.764702	Group1
McDonald's	-36.850191	174.765314	Group1
The Langham	-36.857474	174.764073	Group2
Soul Bar and Bistro	-36.843074	174.763367	Group2
The Hilton	-36.840087	174.765616	Group2

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Modify with this formula

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

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@OwenAuger If you can help 🙂