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 ...
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.

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.