Distance between two coordinates

[AGVEGA]

Calculating the top 3 closest reps to a client. Rank 1 calculate correctly, but when I expand to rank 2 or 3, it doesn't. Any tips? I saw the other post: https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Latitude-Longitude-amp-Top-3-Closest...
I tried that calculation and had the same problem. Only because I tested on Excel and it calculated correctly, did I do the following. 

Dax:

Closest Clinician 1 =

Var COS1=

'client_database'[COS Radian Lat1]

Var SIN1=

'client_database'[Sin Radian Lat1]

VAR Lat1=

'client_database'[latitude]

Var Lon1=

'client_database'[longitude]

VAR Clinicianlist=

ADDCOLUMNS(

    VALUES('cmr_database - 1 All'[Clinician & Discipline]),

    "@Rank",

    RANKX(VALUES('cmr_database - 1 All'[Clinician & Discipline]),

    VAR Lat2=CALCULATE(SELECTEDVALUE('cmr_database - 1 All'[Updated Latitude]))

    VAR Lon2= CALCULATE(SELECTEDVALUE('cmr_database - 1 All'[Updated Longitude]))

    VAR COS2=CALCULATE(SELECTEDVALUE('cmr_database - 1 All'[COS Lat2]))

    VAR SIN2=CALCULATE(SELECTEDVALUE('cmr_database - 1 All'[Sin Lat2]))

    VAR Difference=COS(RADIANS(Lat1-Lat2))

    VAR ACOSCalc=COS1*COS2+SIN1*SIN2*Difference

    Return

    ACOS(ACOSCalc)*3959,

    ,

    ASC

    )

)

return

MAXX(FILTER(Clinicianlist,[@Rank]=1),'cmr_database - 1 All'[Clinician & Discipline])

[Anonymous · Accepted solution]

Hi @AGVEGA ,

According to your statement, I think you want to calculate the top3 client data. I think this might be caused by your filter in return field. You use [@Rank]=1 in filter, I suggest you to try [@Rank]<=3.

Closest Clinician 1 =
VAR COS1 = 'client_database'[COS Radian Lat1]
VAR SIN1 = 'client_database'[Sin Radian Lat1]
VAR Lat1 = 'client_database'[latitude]
VAR Lon1 = 'client_database'[longitude]
VAR Clinicianlist =
    ADDCOLUMNS (
        VALUES ( 'cmr_database - 1 All'[Clinician & Discipline] ),
        "@Rank",
            RANKX (
                VALUES ( 'cmr_database - 1 All'[Clinician & Discipline] ),
                VAR Lat2 =
                    CALCULATE ( SELECTEDVALUE ( 'cmr_database - 1 All'[Updated Latitude] ) )
                VAR Lon2 =
                    CALCULATE ( SELECTEDVALUE ( 'cmr_database - 1 All'[Updated Longitude] ) )
                VAR COS2 =
                    CALCULATE ( SELECTEDVALUE ( 'cmr_database - 1 All'[COS Lat2] ) )
                VAR SIN2 =
                    CALCULATE ( SELECTEDVALUE ( 'cmr_database - 1 All'[Sin Lat2] ) )
                VAR Difference =
                    COS ( RADIANS ( Lat1 - Lat2 ) )
                VAR ACOSCalc = COS1 * COS2 + SIN1 * SIN2 * Difference
                RETURN
                    ACOS ( ACOSCalc ) * 3959,
                ,
                ASC
            )
    )
RETURN
    MAXX (
        FILTER ( Clinicianlist, [@Rank] <= 3 ),
        'cmr_database - 1 All'[Clinician & Discipline]
    )

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AGVEGA]

Thanks Rico! That worked