Anonymous

Not applicable

4 years ago

Solved

[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-latitude-and-longitude

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

Merci beaucoup!

Greg_Deckler

4 years ago

Anonymous 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

Greg_Deckler

4 years ago

Anonymous 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

8 Replies

Greg_Deckler

Community Champion

4 years ago

Anonymous 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

Anonymous
Not applicable
4 years ago
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))

Anonymous

Not applicable

4 years ago

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?

Greg_Deckler

Community Champion

4 years ago

Anonymous 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

Anonymous
Not applicable
4 years ago
Greg_Deckler Thanks, worked perfectly! I dropped you a message inbox. Thanks.

rsimpson318

Frequent Visitor

4 years ago

Greg_Deckler

Sorry to revive an old topic but...

I was able to modify the majority of the calculation provided to get the distance, but when I try to put in the piece that adds zero's to the start of the data (to avoid large numbers) it doesn't seem to be working.

Vehicle	Date/Time	Latitude	Longitude	Distance
11712	4/29/2022 6:47	37.99484	-87.3808	6076.2
11712	4/29/2022 6:48	37.99484	-87.3808	0
11712	4/29/2022 6:52	37.99486	-87.3808	0
11712	4/29/2022 6:56	37.99487	-87.3808	0
11712	4/29/2022 7:00	37.99488	-87.3808	0
11712	4/29/2022 7:04	37.99488	-87.3808	0
11712	4/29/2022 7:08	37.99489	-87.3808	0
11712	4/29/2022 7:12	37.99489	-87.3808	0
11712	4/29/2022 7:16	37.99528	-87.3819	0.1

My data is based on Date/Time rather than "Location", but when it's trying to calculate the distance with the earliest date/time, it causes an issue.

Any help would be greatly appreciated.

Greg_Deckler
Community Champion
4 years ago
rsimpson318 Would have to see your calculation. You should be able to get the earliest date/time using MINX but seems like you have duplicates which is not optimal.
- rsimpson318
  Frequent Visitor
  4 years ago
  Greg_Deckler
  
  This is the code I'm using now:
  
  Distance =
  VAR __NextLocation = MINX(FILTER('ReportData',[Device]=EARLIER([Device]) && [Date/Time] > EARLIER([Date/Time])),[Date/Time])
  VAR __FromLat = [Latitude]
  VAR __ToLat = MINX(FILTER('ReportData',[Device]=EARLIER([Device]) && [Date/Time] = __NextLocation),[Latitude])
  VAR __FromLong = [Longitude]
  VAR __ToLong = MINX(FILTER('ReportData',[Device]=EARLIER([Device]) && [Date/Time] = __NextLocation),[Longitude])
  VAR __C = ACOS(COS(RADIANS(90-__FromLat)) * COS(RADIANS(90-__ToLat)) + SIN(RADIANS(90-__FromLat)) * SIN(RADIANS(90-__ToLat)) * COS(RADIANS(__FromLong-__ToLong))) * 3959
  RETURN
  __C
  
  I modified it slightly based on an Excel formula I found to calculate distance in miles. My issue is that it tries to calculate the last record w/ an empty lat/long, so the final row shows a distance of 6000+ miles:
  
  I was able to sort of get around that by adding an "IF" statement:
  IF(__C>10,0,__C)
  I just wasn't sure if there was a cleaner method to correct it.
  
  Thank you.

Forum Discussion

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

8 Replies

Related content

Direct Lake Model - Calculated Column/Tables

Table Column to Calculate Totals

Adding calculated column to date table

Lookup to unrelated table in calculated column

Calculated column Table - Dates