Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Merci beaucoup!
Solved! Go to Solution.
@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 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
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.
@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.
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 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.
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?
@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 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 Merci à vous ! Works perfectly!
I made slight modifications:
Thank you for your assistance!
VAR __c * (6371)
RETURN
IF(BLANK(__NextLocation),0,(__c))
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |