cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
liamshiff Frequent Visitor
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:

 

 

CustomerLatitudeLongitudeClosest Store (Original Formula)Distance to Closest Store (km)Closest Store2 (New formula)Column 2
Customer 1-36.845538174.760461Soul Bar and Bistro0.376745939876671 Group1
Customer 2-36.825887174.748118The Hilton2.21773122504468 Group1
Customer 3-36.850022174.765985McDonald's0.0625924022793106McDonald'sGroup1
Customer 4-36.855739174.766955The Langham0.320886788770884The LanghamGroup2
Customer 5-36.858394174.760322The Langham0.349053269527966The LanghamGroup2
Customer 6-36.844891174.773084PWC0.717601194877626 Group2

 

 

StoreLatitudeLongitudeColumn 2
PWC-36.843158174.765316Group1
ASB-36.851273174.764702Group1
McDonald's-36.850191174.765314Group1
The Langham-36.857474174.764073Group2
Soul Bar and Bistro-36.843074174.763367Group2
The Hilton-36.840087174.765616Group2
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Distance Calculation with filters

Hi @liamshiff 

Modify with this formula

Capture8.JPG

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.

View solution in original post

2 REPLIES 2
liamshiff Frequent Visitor
Frequent Visitor

Re: Distance Calculation with filters

@OwenAuger If you can help 🙂

 

Community Support Team
Community Support Team

Re: Distance Calculation with filters

Hi @liamshiff 

Modify with this formula

Capture8.JPG

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.

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

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 2019 Community Highlights

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

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 1,771 guests
Please welcome our newest community members: