cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
144Lin
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!  

 

144Lin_0-1631994855597.png

 

 

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
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 __a = (SIN(__distanceLat/2))^2 + COS(RADIANS(__FromLat)) * COS(RADIANS(__ToLat)) * SIN((__distanceLong/2))^2
    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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

@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 __a = (SIN(__distanceLat/2))^2 + COS(RADIANS(__FromLat)) * COS(RADIANS(__ToLat)) * SIN((__distanceLong/2))^2
    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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

5 REPLIES 5
144Lin
Frequent Visitor

 

@Greg_Deckler 

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?

 

144Lin_0-1632161653766.png

 

 

@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 __a = (SIN(__distanceLat/2))^2 + COS(RADIANS(__FromLat)) * COS(RADIANS(__ToLat)) * SIN((__distanceLong/2))^2
    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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

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

Greg_Deckler
Super User
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 __a = (SIN(__distanceLat/2))^2 + COS(RADIANS(__FromLat)) * COS(RADIANS(__ToLat)) * SIN((__distanceLong/2))^2
    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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

@Greg_Deckler  Merci à vous ! Works perfectly! 

I made slight modifications:

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

Thank you for your assistance! 

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

 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors