David,
I think you will require an API to either Google or Bing Maps to calculate the distance by road between two points. Even then I think they will only return a figure which represents the least distance and not the actual route taken.
The calculation of straight line distance between points is know as the Havershine Distance between Points. You can Google it to find numerous examples of how to use the calculation in excel and Power BI.
Below is probably the best Dax example I have found… however, I’d suggest and without knowing your data source, that a better option is to have this as a Power Query custom calculation step when you import your data.
Ref-To Distance =
Var Pi = PI()
Var A1 = [Lat1]* Pi/180
Var A2 = [Long1]* Pi/180
Var B1 = [Lat2] * Pi/180
Var B2 = [Long2] * Pi/180
Var Dlon = Abs(A2 - B2)
Var DLat = Abs(A1 - B1)
Var R = 6371
Return
IF (
A1 = BLANK()|| A2 = BLANK()|| B1 = BLANK() || B2 = BLANK(),
BLANK(),
ROUND(ACOS( SIN(A1)*SIN(B1) + COS(A1) * COS(B1) * COS(Dlon) ) * R, 2))
Hope this helps.
You may find that the equation throws an error of “Acos can not calculate the value” sometimes. I’ve found that by changing the Lat2, Long2 co-ordinates from 8 significant decimals to say 6 allows the calculation to proceed. Just be aware the problem with this is that you will not be able to calculate a 0 distance. Given that GPS is accurate to 15 metres and a change to the level of significance indicated is only 10 - 100 metres error, I don’t think this is a major concern.