Latitude Longitude & Top 3 Closest

[jcampbell474]

Ttying to do something that I think should be simple, but I've hit a wall.

I have the latitude and longitude of two locations. One set in the Rep table and the other set in the Agency table. Also have a radius of the Agency coordinates.  Reps must be within the radius to be considered.

The tables are not joined. I need to get the top 3 reps who are closest to the agency, using their coordinates (three columns - ClosestRep1, ClosestRep2, ClosestRep3).

Feels like I should be able to create a virtual table in DAX, somehow do a lookup or cartesian join of some type to calculate all distances and get the TopN (1-3). 

E.g.,  Distance = ACOS(SIN(Distance[Latitude_1]*[PI_DIV180])*SIN(Distance[Latitude_2]*[PI_DIV180])+COS(Distance[Latitude_1]*[PI_DIV180])*COS(Distance[Latitude_2]*[PI_DIV180])*COS((Distance[Longitude_2]*[PI_DIV180])-(Distance[Longitude_1]*[PI_DIV180])))*3959

How do I first make sure a rep falls inside the radius, then calculate the distance for every rep that does, and finally rank them?  All the while, the tables are not joined.  

Don't know if I'm looking at or thinking about it correctly. If anyone has done something like this, can you please help / point me in the right direction?

[AlexisOlson · Accepted solution]

This is similar to this post I answered a few years ago.

Generalizing so that you can choose a rank different than top 1, I propose the following as a calculated column on the Agency table:

ClosestRep2 =
VAR p = PI () / 360
VAR LatAp = Agency[Latitude] * p
VAR LonAp = Agency[Longitude] * p
VAR RepList =
    ADDCOLUMNS (
        VALUES ( Rep[RepID] ),
        "@Rank",
            RANKX (
                VALUES ( Rep[RepID] ),
                VAR LatRp = CALCULATE ( SELECTEDVALUE ( Rep[Latitude] ) ) * p
                VAR LonRp = CALCULATE ( SELECTEDVALUE ( Rep[Longitude] ) ) * p
                RETURN
                    ASIN (
                        SQRT (
                            SIN ( LatAp - LatRp ) ^ 2
                                + COS ( 2 * LatAp ) * COS ( 2 * LatRp )
                                    * SIN ( LonAp - LonRp ) ^ 2
                        )
                    ) * 3959 * 2,
                ,
                ASC
            )
    )
RETURN
    MAXX ( FILTER ( RepList, [@Rank] = 2 ), Rep[RepID] )

[AlexisOlson · Accepted solution]

This is similar to this post I answered a few years ago.

Generalizing so that you can choose a rank different than top 1, I propose the following as a calculated column on the Agency table:

ClosestRep2 =
VAR p = PI () / 360
VAR LatAp = Agency[Latitude] * p
VAR LonAp = Agency[Longitude] * p
VAR RepList =
    ADDCOLUMNS (
        VALUES ( Rep[RepID] ),
        "@Rank",
            RANKX (
                VALUES ( Rep[RepID] ),
                VAR LatRp = CALCULATE ( SELECTEDVALUE ( Rep[Latitude] ) ) * p
                VAR LonRp = CALCULATE ( SELECTEDVALUE ( Rep[Longitude] ) ) * p
                RETURN
                    ASIN (
                        SQRT (
                            SIN ( LatAp - LatRp ) ^ 2
                                + COS ( 2 * LatAp ) * COS ( 2 * LatRp )
                                    * SIN ( LonAp - LonRp ) ^ 2
                        )
                    ) * 3959 * 2,
                ,
                ASC
            )
    )
RETURN
    MAXX ( FILTER ( RepList, [@Rank] = 2 ), Rep[RepID] )

[jcampbell474]

This is excellent!  Thank you so much!!

Appears to work, so far.  I'm validating against another app.   

Modified the code below to integrate into the pbix.  What is the best way to get distance from it?  I just need to list:

ClosestRep1: RepName

ClosestRep1: Distance

thru

Closest Rep3.

ClosestRep1 = 
VAR p = PI () / 360
VAR LatAp = SPOC[AgencyLatitude] * p
VAR LonAp = SPOC[AgencyLongitude] * p
VAR RepList =
    ADDCOLUMNS (
        VALUES ( SalesRepLocation[Name] ),
        "@Rank",
            RANKX (
                VALUES ( SalesRepLocation[Name] ),
                VAR LatRp = CALCULATE ( SELECTEDVALUE ( SalesRepLocation[Latitude] ) ) * p
                VAR LonRp = CALCULATE ( SELECTEDVALUE ( SalesRepLocation[Longitude] ) ) * p
                RETURN
                    ASIN (
                        SQRT (
                            SIN ( LatAp - LatRp ) ^ 2
                                + COS ( 2 * LatAp ) * COS ( 2 * LatRp )
                                    * SIN ( LonAp - LonRp ) ^ 2
                        )
                    ) * 3959,
                ,
                ASC
            )
    )
RETURN
    MAXX ( FILTER ( RepList, [@Rank] = 1 ), SalesRepLocation[Name] )

[AlexisOlson · Accepted solution]

It might be easier to pull out the distance if you calculate that first and then compute the ranking:

Distance2 = 
VAR p = PI () / 360
VAR LatAp = Agency[Latitude] * p
VAR LonAp = Agency[Longitude] * p
VAR RepList =
    ADDCOLUMNS (
        VALUES ( Rep[Rep] ),
        "@Distance",
            VAR LatRp = CALCULATE ( SELECTEDVALUE ( Rep[Latitude] ) ) * p
            VAR LonRp = CALCULATE ( SELECTEDVALUE ( Rep[Longitude] ) ) * p
            RETURN
                ASIN (
                    SQRT (
                        SIN ( LatAp - LatRp ) ^ 2
                            + COS ( 2 * LatAp ) * COS ( 2 * LatRp )
                                * SIN ( LonAp - LonRp ) ^ 2
                    )
                ) * 3959 * 2
    )
VAR RepsRanked =
    ADDCOLUMNS ( RepList, "@Rank", RANKX ( RepList, MAX ( [@Distance] ), , ASC ) )
RETURN
    MAXX (
        FILTER ( RepsRanked, [@Rank] = 2 ),
        [@Distance]
    )

[jcampbell474]

Excellent!  Yes, it does provide the distance!  Maybe I'm doing something wrong, but it seems to be calculating approx. half of what the distance should be. 

Sample Coordinates:

AgencyLatitude:  40.16581726

AgencyLongitude: -75.29247284

RepLatitude: 40.077736

RepLongitude: -75.30509

DAX Expression says 3.63 miles.  GPS Latitude and Longitude Distance Calculator (csgnetwork.com), says ~6 miles. 

I'm likely doing something wrong.  Any idea what it is? 

This is exactly what I'm using:

Distance1 = 
VAR p = PI () / 360
VAR LatAp = SPOC[AgencyLatitude] * p
VAR LonAp = SPOC[AgencyLongitude] * p
VAR RepList =
    ADDCOLUMNS (
        VALUES ( SalesRepLocation[Name] ),
        "@Distance",
            VAR LatRp = CALCULATE ( SELECTEDVALUE ( SalesRepLocation[Latitude] ) ) * p
            VAR LonRp = CALCULATE ( SELECTEDVALUE ( SalesRepLocation[Longitude] ) ) * p
            RETURN
                ASIN (
                    SQRT (
                        SIN ( LatAp - LatRp ) ^ 2
                            + COS ( 2 * LatAp ) * COS ( 2 * LatRp )
                                * SIN ( LonAp - LonRp ) ^ 2
                    )
                ) * 3959
    )
VAR RepsRanked =
    ADDCOLUMNS ( RepList, "@Rank", RANKX ( RepList, MAX ( [@Distance] ), , ASC ) )
RETURN
    MAXX (
        FILTER ( RepsRanked, [@Rank] = 1 ),
        [@Distance]
    )

[jcampbell474]

Think I figured it out.  var P was was divided by 360.  Should be 180 since it's a radius.

[jcampbell474]

Actually, that didn't work.  I'm now multiplying the Distance by 2 and it works out.  Is this fix okay?

[AlexisOlson]

That's what I was suggesting in my last comment, yes. Double the radius, not p.

[AlexisOlson]

Ah, right. I accidentally dropped the doubling of the radius. Please replace "* 3959" with "* 3959 * 2".

[jcampbell474]

Sorry about that.  I hadn't gotten the email notification (it's been slow all day) and didn't refresh.  My bad.

I doubled the radius and all looks good.  You saved me at least a day of work.  I really appreciate your help!  Thank you so much!!

[AlB]

Hi @jcampbell474 

Where do you need the result, in calculated columns?

Yes, creating a virtual table that first calculates the distance to the agency, second takes the top 3 and third filters out the ones that whose distance is above the radius.

If you want a more specific answer show some sample input data and the expected result for that data.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.