cancel
Showing results for 
Search instead for 
Did you mean: 
V-lianl-msft

Calculate the Distance between Two Locations

Basic scenario:

When we want to calculate the distance between two locations, we need to know the coordinates of these two locations.

Here’s the sample data:

Vlianlmsft_0-1633590528313.png

 

We now have the coordinates of current locations and destinations, using below formula we could get the distance between these locations.

Distince (KM) =
12742 -- The diameter of the Earth (KM).
    * ASIN (
        -- Returns the arcsine, or inverse sine, of a number.
        SQRT (
            -- Returns the square root of a number.
            POWER (
                -- Returns the result of a number raised to a power.
                SIN ( -- Returns the sine of the given angle.
                'Table'[Current Lat] - 'Table'[Destination Lat] )
                    * PI () / 360,
                2
            ) -- PI () Returns the value of Pi, 3.14159265358979, accurate to 15 digits.
                + COS (
                    -- Returns the cosine of the given angle.
                    'Table'[Current Lat] * PI () / 180
                )
                    * COS ( 'Table'[Destination Lat] * PI () / 180 )
                    * POWER (
                        SIN ( 'Table'[Current Long] - 'Table'[Destination Long] )
                            * PI () / 360,
                        2
                    )
        )
    )

Vlianlmsft_1-1633590660490.png

 

Reference:

https://docs.microsoft.com/en-us/dax/sqrt-function-dax 

https://docs.microsoft.com/en-us/dax/asin-function-dax 

https://docs.microsoft.com/en-us/dax/power-function-dax 

https://docs.microsoft.com/en-us/dax/sin-function-dax 

https://docs.microsoft.com/en-us/dax/cos-function-dax 

https://docs.microsoft.com/en-us/dax/pi-function-dax 

 

Advanced scenario:

Now we have two tables.

The Customer table contains the coordinates of some customers.

Vlianlmsft_2-1633590682187.png

 

And the Store table contains the coordinates of some stores.

Vlianlmsft_3-1633590692107.png

 

By flexibly using the above formula of calculating distance, we can get the nearest store for each user and also get the distance from each user to their nearest store.

Nearest Store =
CALCULATE (
    FIRSTNONBLANK ( Store[Store], 0 ),
    TOPN (
        1,
        Store,
        12742
            * ASIN (
                SQRT (
                    POWER ( SIN ( Customer[Latitude] - Store[Latitude] ) * PI () / 360, 2 )
                        + COS ( Customer[Latitude] * PI () / 180 )
                            * COS ( Store[Latitude] * PI () / 180 )
                            * POWER ( SIN ( Customer[Longitude] - Store[Longitude] ) * PI () / 360, 2 )
                )
            ), ASC
    )
)
Distance to Nearest Store (KM) =
MINX (
    Store,
    12742
        * ASIN (
            SQRT (
                POWER ( SIN ( Customer[Latitude] - Store[Latitude] ) * PI () / 360, 2 )
                    + COS ( Customer[Latitude] * PI () / 180 )
                        * COS ( Store[Latitude] * PI () / 180 )
                        * POWER ( SIN ( Customer[Longitude] - Store[Longitude] ) * PI () / 360, 2 )
            )
        )
)

 

Vlianlmsft_4-1633590800255.png

 

 

Author: Jay Wang

Reviewer: Ula Huang, Kerry Wang

Comments
Polls
What is your favorite Power BI Feature release this month?