My data looks like this, a place with their coordinates details
Place Latitude Longitude
A 2.314 97.6110288
B 3.425 98.6925504
C 4.1231 99.774072
D 5.096466667 100.8555936
E 6.001016667 101.9371152
F 6.905566667 103.0186368
G 7.810116667 104.1001584
H 8.714666667 105.18168
I 9.619216667 106.2632016
J 10.52376667 107.3447232
K 11.42831667 108.4262448
L 12.33286667 109.5077664
M 13.23741667 110.589288
N 14.14196667 111.6708096
O 15.04651667 112.7523312
P 15.95106667 113.8338528
So the table looks like this what i want to do is compare the place to all the other place by counting the distance in between places. and if it fulfills the criteria, we add one to output
so for example
We compare the distance of Place A to , B,C,D,E,F,G
so
for example A-B , distance = 100
A-C, distance = 70
A-D, distance = 50
A-E,distance = 120
A-F,distance = 140
A-G,distance = 175
A-H, DIstance=80
A-I,Distance =40
A-J,Distance=190
A-K,distance=209
A-L,distance=109
A-M,A-N,A-O,A-P=150
and we go a conditional so if i want to only take the one that is larger than 151 , it will return 3 for the row
and this will calculate for all rows in the table
the output example is like this
output expected
Place Latitude Longitude Bigger Than 151
A 2.314 97.6110288 3
B 3.425 98.6925504 5
C 4.1231 99.774072 1
D 5.096466667 100.8555936 3
E 6.001016667 101.9371152 2
F 6.905566667 103.0186368 1
G 7.810116667 104.1001584 5
H 8.714666667 105.18168 2
I 9.619216667 106.2632016 4
J 10.52376667 107.3447232 1
K 11.42831667 108.4262448 0
L 12.33286667 109.5077664 0
M 13.23741667 110.589288 0
N 14.14196667 111.6708096 0
O 15.04651667 112.7523312 0
P 15.95106667 113.8338528 0
i also can use python for power bi, if power query/dax power Bi may not be able to solve this .
Thank you