Forum Discussion

PSB's avatar
PSB
Helper III
3 years ago
Solved

Help calculate distance using DAX Query (using math formula)

I need help calculate distance using lat and long information.

Using math formula shown below (its' tested and working but when source and target lat and long information is in same table)

In this scenario, one table I have Source and target and need distance between them and the lat and long values needs to be looked up from another table.

Any help is appreciated. Thanks. 

 

 

FromToDistance (Miles)
BerlinTokyo?
MumbaiNew York?
LondonParis?

 

CityLatLong
Berlin39.58305-74.78388977
Mumbai39.55434-74.74279785
London39.51902-74.69287872
Tokyo39.48972-74.59777832
New York39.45287-74.63844299
Paris39.43-74.57861328

------------------------------------------

Sample

-------------------------------------------

Distance (Miles) =
var Lat1 = MIN(SA_to_SA_Market_Cell_Relation_Counters_Copy[S_Lat])
var Lng1 = MIN(SA_to_SA_Market_Cell_Relation_Counters_Copy[S_Long])

var Lat2 = MIN(SA_to_SA_Market_Cell_Relation_Counters_Copy[T_Lat])
var Lng2 = MIN(SA_to_SA_Market_Cell_Relation_Counters_Copy[T_Long])
---- Algorithm here -----
var P = DIVIDE( PI(), 180 )
var A = 0.5 - COS((Lat2-Lat1) * p)/2 +
    COS(Lat1 * p) * COS(lat2 * P) * (1-COS((Lng2- Lng1) * p))/2
var final = 7922 * ASIN((SQRT(A))) --2*r (12742 for km
return final

 

Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    Distance = 
var Lat1=LOOKUPVALUE(Locations[Lat],Locations[City],SELECTEDVALUE(Distances[From]))
var Lng1=LOOKUPVALUE(Locations[Long],Locations[City],SELECTEDVALUE(Distances[From]))
var Lat2 =LOOKUPVALUE(Locations[Lat],Locations[City],SELECTEDVALUE(Distances[To]))
var Lng2 =LOOKUPVALUE(Locations[Long],Locations[City],SELECTEDVALUE(Distances[To]))
var P = DIVIDE(PI(),180)
var A = 0.5 - COS((Lat2-Lat1) * P)/2 + COS(Lat1 * P) * COS(lat2 * P) * (1-COS((Lng2- Lng1) * P))/2
return 12742 * ASIN((SQRT(A)))

     

    see attached.

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    Distance = 
var Lat1=LOOKUPVALUE(Locations[Lat],Locations[City],SELECTEDVALUE(Distances[From]))
var Lng1=LOOKUPVALUE(Locations[Long],Locations[City],SELECTEDVALUE(Distances[From]))
var Lat2 =LOOKUPVALUE(Locations[Lat],Locations[City],SELECTEDVALUE(Distances[To]))
var Lng2 =LOOKUPVALUE(Locations[Long],Locations[City],SELECTEDVALUE(Distances[To]))
var P = DIVIDE(PI(),180)
var A = 0.5 - COS((Lat2-Lat1) * P)/2 + COS(Lat1 * P) * COS(lat2 * P) * (1-COS((Lng2- Lng1) * P))/2
return 12742 * ASIN((SQRT(A)))

     

    see attached.

    • PSB's avatar
      PSB
      Helper III
      3 years ago

      Thanks! you're the best.