Forum Discussion

AGVEGA's avatar
AGVEGA
Frequent Visitor
2 years ago
Solved

Distance between two coordinates

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/m-p/1912483
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's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.