Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

[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

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

8 REPLIES 8
rsimpson318
Frequent Visitor

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

VehicleDate/TimeLatitudeLongitudeDistance
117124/29/2022 6:4737.99484-87.38086076.2
117124/29/2022 6:4837.99484-87.38080
117124/29/2022 6:5237.99486-87.38080
117124/29/2022 6:5637.99487-87.38080
117124/29/2022 7:0037.99488-87.38080
117124/29/2022 7:0437.99488-87.38080
117124/29/2022 7:0837.99489-87.38080
117124/29/2022 7:1237.99489-87.38080
117124/29/2022 7:1637.99528-87.38190.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.

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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:
rsimpson318_0-1651603088231.png

 

 

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.

Anonymous
Not applicable

 

@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

 

 

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Greg_Deckler
Super User
Super User

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors