cancel
Showing results for
Did you mean:
Frequent Visitor

## [HELP NEEDED] : Distance calculation with (latlong) in same table/column.

Bonsoir BI community !

Anyone ever tried the calculating distance using long lat in same columns before? I am working on an academic project and want to calculate the distance btween two stores per country. FOr example, IN China, (Location_1_distance = distance between loc_1 and loc_2 like that like that continously) but by country.

The examples in the two links below applied location one and two in different columns which is different from mine situation, in addition, I was to apply the query by country. Please let me know if you can help ! Sample data below.

https://stackoverflow.com/questions/55557376/how-to-find-the-distancemiles-between-2-points-using-la...

https://community.powerbi.com/t5/Desktop/How-to-calculate-varience-of-distance-between-locations-in-...

Merci beaucoup!

2 ACCEPTED SOLUTIONS
Super User

@144Lin Just use Going the Distance: Going the Distance - Microsoft Power BI Community

Like this:

``````Column =
VAR __NextLocation = MINX(FILTER('Table',[Country]=EARLIER([Country]) && [Location] > EARLIER([Location])),[Location])
VAR __FromLat = [lat]
VAR __ToLat = MINX(FILTER('Table15',[Country]=EARLIER([Country]) && [Location] = __NextLocation),[lat])
VAR __FromLong = [lng]
VAR __ToLong = MINX(FILTER('Table15',[Country]=EARLIER([Country]) && [Location] = __NextLocation),[lng])
VAR __distanceLong = RADIANS(__ToLong - __FromLong)
VAR __distanceLat = RADIANS(__ToLat - __FromLat)
VAR __y = SQRT(__a)
VAR __x = SQRT(1 - __a)
VAR __atan2 =
SWITCH(
TRUE(),
__x > 0, ATAN(__y/__x),
__x < 0 && __y >= 0, ATAN(__y/__x) + PI(),
__x < 0 && __y < 0, ATAN(__y/__x) - PI(),
__x = 0 && __y > 0, PI()/2,
__x = 0 && __y < 0, PI()/2 * (0-1),
BLANK()
)
VAR __c = 2 * __atan2
RETURN
__c``````

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Super User

@144Lin Try:

``````Column =
VAR __NextLocation = MAXX(FILTER('Table',[Country]=EARLIER([Country]) && [Location] < EARLIER([Location])),[Location])
VAR __FromLat = [lat]
VAR __ToLat = MINX(FILTER('Table15',[Country]=EARLIER([Country]) && [Location] = __NextLocation),[lat])
VAR __FromLong = [lng]
VAR __ToLong = MINX(FILTER('Table15',[Country]=EARLIER([Country]) && [Location] = __NextLocation),[lng])
VAR __distanceLong = RADIANS(__ToLong - __FromLong)
VAR __distanceLat = RADIANS(__ToLat - __FromLat)
VAR __y = SQRT(__a)
VAR __x = SQRT(1 - __a)
VAR __atan2 =
SWITCH(
TRUE(),
__x > 0, ATAN(__y/__x),
__x < 0 && __y >= 0, ATAN(__y/__x) + PI(),
__x < 0 && __y < 0, ATAN(__y/__x) - PI(),
__x = 0 && __y > 0, PI()/2,
__x = 0 && __y < 0, PI()/2 * (0-1),
BLANK()
)
VAR __c = 2 * __atan2
RETURN
__c``````

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

5 REPLIES 5
Frequent Visitor

Sorry I am coming back on this as I noticed an error I made during my review. I think I need a slight help further. In fact, from Location 1 to Location 2 (as location 1 has no previous initial value, the distance will is zero, but the result is the other way around). I have tried to re-write the query but not getting the answer, please can you help me?

Super User

@144Lin Try:

``````Column =
VAR __NextLocation = MAXX(FILTER('Table',[Country]=EARLIER([Country]) && [Location] < EARLIER([Location])),[Location])
VAR __FromLat = [lat]
VAR __ToLat = MINX(FILTER('Table15',[Country]=EARLIER([Country]) && [Location] = __NextLocation),[lat])
VAR __FromLong = [lng]
VAR __ToLong = MINX(FILTER('Table15',[Country]=EARLIER([Country]) && [Location] = __NextLocation),[lng])
VAR __distanceLong = RADIANS(__ToLong - __FromLong)
VAR __distanceLat = RADIANS(__ToLat - __FromLat)
VAR __y = SQRT(__a)
VAR __x = SQRT(1 - __a)
VAR __atan2 =
SWITCH(
TRUE(),
__x > 0, ATAN(__y/__x),
__x < 0 && __y >= 0, ATAN(__y/__x) + PI(),
__x < 0 && __y < 0, ATAN(__y/__x) - PI(),
__x = 0 && __y > 0, PI()/2,
__x = 0 && __y < 0, PI()/2 * (0-1),
BLANK()
)
VAR __c = 2 * __atan2
RETURN
__c``````

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Frequent Visitor

@Greg_Deckler  Thanks, worked perfectly! I dropped you a message inbox. Thanks.

Super User

@144Lin Just use Going the Distance: Going the Distance - Microsoft Power BI Community

Like this:

``````Column =
VAR __NextLocation = MINX(FILTER('Table',[Country]=EARLIER([Country]) && [Location] > EARLIER([Location])),[Location])
VAR __FromLat = [lat]
VAR __ToLat = MINX(FILTER('Table15',[Country]=EARLIER([Country]) && [Location] = __NextLocation),[lat])
VAR __FromLong = [lng]
VAR __ToLong = MINX(FILTER('Table15',[Country]=EARLIER([Country]) && [Location] = __NextLocation),[lng])
VAR __distanceLong = RADIANS(__ToLong - __FromLong)
VAR __distanceLat = RADIANS(__ToLat - __FromLat)
VAR __y = SQRT(__a)
VAR __x = SQRT(1 - __a)
VAR __atan2 =
SWITCH(
TRUE(),
__x > 0, ATAN(__y/__x),
__x < 0 && __y >= 0, ATAN(__y/__x) + PI(),
__x < 0 && __y < 0, ATAN(__y/__x) - PI(),
__x = 0 && __y > 0, PI()/2,
__x = 0 && __y < 0, PI()/2 * (0-1),
BLANK()
)
VAR __c = 2 * __atan2
RETURN
__c``````

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Frequent Visitor

@Greg_Deckler  Merci à vous ! Works perfectly!

• I multiplied by 6371 (average earth radium in Km)
• I also applied IF(ISBLANK) to make sure the first row per country is zero, otherwise, it gives large value numbers.

``````   VAR __c * (6371)
RETURN
IF(BLANK(__NextLocation),0,(__c))``````