Distance Calculation with filters

Question

hello, everyone,&nbsp;&nbsp;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.&nbsp;&nbsp;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.&nbsp;&nbsp;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?&nbsp;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:&nbsp;&nbsp;CustomerLatitudeLongitudeClosest Store (Original Formula)Distance to Closest Store (km)Closest Store2 (New formula)Column 2Customer 1-36.845538174.760461Soul Bar and Bistro0.376745939876671&nbsp;Group1Customer 2-36.825887174.748118The Hilton2.21773122504468&nbsp;Group1Customer 3-36.850022174.765985McDonald's0.0625924022793106McDonald'sGroup1Customer 4-36.855739174.766955The Langham0.320886788770884The LanghamGroup2Customer 5-36.858394174.760322The Langham0.349053269527966The LanghamGroup2Customer 6-36.844891174.773084PWC0.717601194877626&nbsp;Group2&nbsp;&nbsp;StoreLatitudeLongitudeColumn 2PWC-36.843158174.765316Group1ASB-36.851273174.764702Group1McDonald's-36.850191174.765314Group1The Langham-36.857474174.764073Group2Soul Bar and Bistro-36.843074174.763367Group2The Hilton-36.840087174.765616Group2

v-juanli-msft · Accepted Answer

Hi&nbsp;Anonymous&nbsp;
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 RegardsMaggie
Community Support Team _ Maggie LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Distance Calculation with filters

2 Replies

Related content

How to calculate lat/long distance

Calculating with Filters

Distance Calculation in Power BI

Calculate distance between Zip Codes?

Calculating Sequential Distance in Power BI

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