Forum Discussion
Solved
Top 3 nearest locations dax
It doesn't work past rank 1, any tips? I am trying to calculate the top three closest representatives.
Closest Clinician 2 =
Var COS1=
'public client_database'[COS Radian Lat1]
Var SIN1=
'public client_database'[Sin Radian Lat1]
VAR Lat1=
'public client_database'[latitude]
Var Lon1=
'public client_database'[longitude]
VAR Clinicianlist=
ADDCOLUMNS(
SUMMARIZECOLUMNS('public cmr_database - 1 All'[Clinician & Discipline]),
"@Rank",
RANKX(VALUES('public cmr_database - 1 All'[Clinician & Discipline]),
VAR Lat2=CALCULATE(SELECTEDVALUE('public cmr_database - 1 All'[Updated Latitude]))
VAR Lon2= CALCULATE(SELECTEDVALUE('public cmr_database - 1 All'[Updated Longitude]))
VAR COS2=CALCULATE(SELECTEDVALUE('public cmr_database - 1 All'[COS Lat2]))
VAR SIN2=CALCULATE(SELECTEDVALUE('public cmr_database - 1 All'[Sin Lat2]))
VAR Difference=COS(RADIANS(Lon1-Lon2))
VAR ACOSCalc=SIN(Lat1*PI()/180)*SIN(Lat2*PI()/180)+COS(Lat1*PI()/180)*COS(Lat2*PI()/180)*COS((Lon2*PI()/180)-(Lon1*PI()/180))
Return
IFERROR(ACOS(ACOSCalc)*3959,BLANK()),
,
ASC
)
)
return
MAXX(FILTER(Clinicianlist,[@Rank]=2),'public cmr_database - 1 All'[Clinician & Discipline])
Hi AGVEGA what is output of your measure? Error maybe?
If yes, check link below as function SUMMARIZECOLUMNS could not be used as part of measure.
Try to debug / rework you part
ADDCOLUMNS(SUMMARIZECOLUMNS('public cmr_database - 1 All'[Clinician & Discipline]),"@Rank",RANKX(VALUES('public cmr_database - 1 All'[Clinician & Discipline]),Hope this help / kudos appreciated.
1 Reply
- some_bih
Community Champion
Hi AGVEGA what is output of your measure? Error maybe?
If yes, check link below as function SUMMARIZECOLUMNS could not be used as part of measure.
Try to debug / rework you part
ADDCOLUMNS(SUMMARIZECOLUMNS('public cmr_database - 1 All'[Clinician & Discipline]),"@Rank",RANKX(VALUES('public cmr_database - 1 All'[Clinician & Discipline]),Hope this help / kudos appreciated.