Forum Discussion
Problem calculating distance between two points with Latitude and longitude
Hi, im having some issues creating a new column that calculates the distance between two points.
I have the latitude and longitude of both points, created a new column to give me the kilometers between these two points but all i got is a 0 in return.
Below is the formula im using and a screenshot of the points i have.
KM =
var x1 = RADIANS(SELECTEDVALUE(DMS[LAT OR]))
var x2 = RADIANS(SELECTEDVALUE(DMS[LAT DEST]))
var y1 = RADIANS(SELECTEDVALUE(DMS[LON OR]))
var y2 = RADIANS(SELECTEDVALUE(DMS[LON DEST]))
var R = 6371
var lcos = ROUND(SIN(x1)*SIN(x2)+cos(x1)*cos(x2)*cos(y1-y2),15)
var arco = ACOS(lcos)
return
arco*R
This is a column, so you should not use SELECTEDVALUE
The formula you wrote would work great in a measure, but not in a column. The column should be:
KM = var x1 = RADIANS(DMS[LAT OR]) var x2 = RADIANS(DMS[LAT DEST]) var y1 = RADIANS(DMS[LON OR]) var y2 = RADIANS(DMS[LON DEST]) var R = 6371 var lcos = SIN(x1)*SIN(x2)+cos(x1)*cos(x2)*cos(y1-y2) var arco = ACOS(lcos) return arco*RAlthough using a measure would be much better for memory if you increase your table size.
3 Replies
This is a column, so you should not use SELECTEDVALUE
The formula you wrote would work great in a measure, but not in a column. The column should be:
KM = var x1 = RADIANS(DMS[LAT OR]) var x2 = RADIANS(DMS[LAT DEST]) var y1 = RADIANS(DMS[LON OR]) var y2 = RADIANS(DMS[LON DEST]) var R = 6371 var lcos = SIN(x1)*SIN(x2)+cos(x1)*cos(x2)*cos(y1-y2) var arco = ACOS(lcos) return arco*RAlthough using a measure would be much better for memory if you increase your table size.
SteveCampbell ,
If I would like to convert this into 'Miles' then how the measure & calc column could be modified? Could you please help me with that ?
Thanks in advance
Hi tayusso. Nice to meet you. I wonder if you have found a solution to this problem:
Solved: Problem calculating distance between two points wi... - Microsoft Power BI Community
I have the same problem and I can't find a solution.
In a column, if you write the number directly inside the ACOS() function it returns a correct answer, like this: Distance = ACOS(0.896494) = 2.924,31 Km
Could you help me please?
Felipe Baritto (Venezuela)
