Forum Discussion

tanvi_m's avatar
tanvi_m
Frequent Visitor
7 years ago

Rank based on distance measure

I have three tables which i am using in order to calculate the distance between two lat/longs. 

 

Table A : 

X, lat , long

 

Table " From A"  : 

From A = SUMMARIZECOLUMNS(

'A'[X],
'A'[LATITUDE],
'A'[LONGITUDE]
)

 

Table B:

 SITE ID CI128 HXL = SUMMARIZECOLUMNS('A'[X])

 

measure to calculate distance : 

mile =
var lat1 = min('From A'[LATITUDE])
var lng1 = min('From A'[LONGITUDE])
var lat2 = min ('A'[LATITUDE])
var long2 = min ('A'[LONGITUDE])
var P = divide (pi(),180)
var A = 0.5 - cos((lat2-lat1)*p)/2+
cos(lat1*p)*cos(lat2*p)*(1-cos((long2-lng1)*p))/2
var final = 0.621 * (12742 * asin((sqrt(A))))
return final
 
Now I have my distance calculated perfectecly when I put following entries in the table frmat 
 
X(Source - coming from table A) | X(Target - coming from table "From A) | mile  . 
 
Now I need to write a measure in order to rank them based on their distances 
 
X(source) | X(Target) | mile |  RANK (?) 
 
Could anyone please help me with the same ? 
 
Thanks 
Need Help

2 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    7 years ago

    Hey,

     

    please provide a pbix file that contains some sample data, but still represents the data model, upload the file to onderive, dropbox, and share the link (if you use xlsx for the data creation, upload the xlsx file as well).

     

    Regards,

    Tom

  • tanvi_m's avatar
    tanvi_m
    Frequent Visitor
    7 years ago

    Tom, 

    Thankyou for the response . 

    Here’s the complete details on data structure 

     

    Table A : 

    LONGITUDE

    LATITUDE

    X

    -96.6

    32.9

    A

    -96.9

    33

    B

    -96.5

    32

    C

    -97.7

    31

    D

    -96

    32.2

    A

    -97

    32.1

    B

    -96.5

    33

    E

    -97.7

    31

    X

     

    Table B = SUMMARIZECOLUMNS('A'[X])

    From A = SUMMARIZECOLUMNS(

    'A'[X],

    'A'[LATITUDE],

    'A'[LONGITUDE]

    )

     

    OUTPUT TABLE : 

    X (coming from table A)

    X(Coming from table From A)

    Distance(miles)

    A

    C

    14.99815

    C

    A

    14.99815

    A

    B

    24.38354

    B

    A

    24.38354

     

    Now I need to rank column [ X(Coming from table From A) ] based on values in column " distance(miles)" . 

    Hope this explains the query in details.. 

     

    Thankyou 

    Tanvi