cancel
Showing results for
Did you mean:
Highlighted
liamshiff Frequent Visitor

## Distance Calculation with filters

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
1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

## Re: Distance Calculation with filters

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.
2 REPLIES 2
liamshiff Frequent Visitor

## Re: Distance Calculation with filters

@OwenAuger If you can help 🙂 Community Support Team

## Re: Distance Calculation with filters

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.

Announcements #### Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future. #### October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.  #### Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI. Top Kudoed Authors
Users Online
Currently online: 6 members 1,771 guests
Recent signins:
• BenTheSmaller • aartiladdha • anky86 • fairbask • MFry8890 • jesusmoreno • ShikhaPakhide • Kartikb • CW_Keeley • minkeyqi • pwrxman • qinh0402 • Niraj 