topic Re: Distance between two coordinates in DAX Commands and Tips

Distance between two coordinates

AGVEGA — Wed, 30 Aug 2023 17:40:05 GMT

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])

Re: Distance between two coordinates

Anonymous — Fri, 01 Sep 2023 07:09:23 GMT

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.

Re: Distance between two coordinates

AGVEGA — Tue, 05 Sep 2023 16:33:49 GMT

Thanks Rico! That worked