Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I need to calculate the bearing in degrees between two sets of latitude /longitude points in a DAX formula. I can do this in Power Query, but need it in DAX.
Thank you.
Paul
Solved! Go to Solution.
I believe this is it. Attached PBIX matches with my Excel file, will post both.
Bearing (Degrees) =
VAR __FromCity = SELECTEDVALUE('From City'[City])
VAR __ToCity = SELECTEDVALUE('To City'[City])
VAR __FromLat = RADIANS(LOOKUPVALUE('Table'[Latitude],'Table'[City],__FromCity))
VAR __ToLat = RADIANS(LOOKUPVALUE('Table'[Latitude],'Table'[City],__ToCity))
VAR __FromLong = RADIANS(LOOKUPVALUE('Table'[Longitude],'Table'[City],__FromCity))
VAR __ToLong = RADIANS(LOOKUPVALUE('Table'[Longitude],'Table'[City],__ToCity))
VAR __distanceLong = (__ToLong - __FromLong)
VAR __y = COS(__ToLat) * SIN(__distanceLong)
VAR __x = COS(__FromLat) * SIN(__ToLat) - SIN(__FromLat) * COS(__ToLat) * COS(__distanceLong)
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()
)
RETURN
DEGREES(__atan2)
Bearing 2 = MOD([Bearing (Degrees)]+360,360)
Not sure about bearing in dergee. But for distance you can refer : https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-h...
Thank you, indeed this is for distance, and it works. Bearing seems another matter, surprisingly little mentioned about it in DAX.
@Paulus - How is this? PBIX is attached. Please confirm you get the right answers for your data.
b =
VAR __FromCity = SELECTEDVALUE('From City'[City])
VAR __ToCity = SELECTEDVALUE('To City'[City])
VAR __FromLat = LOOKUPVALUE('Table'[Latitude],'Table'[City],__FromCity)
VAR __ToLat = LOOKUPVALUE('Table'[Latitude],'Table'[City],__ToCity)
VAR __FromLong = LOOKUPVALUE('Table'[Longitude],'Table'[City],__FromCity)
VAR __ToLong = LOOKUPVALUE('Table'[Longitude],'Table'[City],__ToCity)
VAR __distanceLong = RADIANS(ABS(__ToLong - __FromLong))
VAR __x = COS(RADIANS(__ToLat)) * SIN(__distanceLong)
VAR __y = COS(RADIANS(__FromLat)) * SIN(RADIANS(__ToLat)) - SIN(RADIANS(__FromLat)) * COS(RADIANS(__ToLat)) * COS(__distanceLong)
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()
)
RETURN
DEGREES(__atan2)
Hi Greg,
Thanks.
Not quite yet, the result between Kansas and St.Louis returns -6,5126..... it should be 97 degrees.
Paul
I believe this is it. Attached PBIX matches with my Excel file, will post both.
Bearing (Degrees) =
VAR __FromCity = SELECTEDVALUE('From City'[City])
VAR __ToCity = SELECTEDVALUE('To City'[City])
VAR __FromLat = RADIANS(LOOKUPVALUE('Table'[Latitude],'Table'[City],__FromCity))
VAR __ToLat = RADIANS(LOOKUPVALUE('Table'[Latitude],'Table'[City],__ToCity))
VAR __FromLong = RADIANS(LOOKUPVALUE('Table'[Longitude],'Table'[City],__FromCity))
VAR __ToLong = RADIANS(LOOKUPVALUE('Table'[Longitude],'Table'[City],__ToCity))
VAR __distanceLong = (__ToLong - __FromLong)
VAR __y = COS(__ToLat) * SIN(__distanceLong)
VAR __x = COS(__FromLat) * SIN(__ToLat) - SIN(__FromLat) * COS(__ToLat) * COS(__distanceLong)
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()
)
RETURN
DEGREES(__atan2)
Bearing 2 = MOD([Bearing (Degrees)]+360,360)
Great!, Thanks. I have been struggling with this for quite a while, digging as hole for myself, not being in my comfort zone.
Briefly why I need it; deliveries to droppoints, many of them available, service engineer on his way to work, in his allocated working area, needs to collect spare parts from a droppoint. The nearest droppoint may be say 25 miles but in southern direction, whereas his allocated working area is in the North. I convert degrees into something like 0-22,5 = N 22,6-66,5= NE etc.
Paul
Hi @Paulus
Im not sure what do you mean exactly but try a solution by @Greg_Deckler https://community.powerbi.com/t5/Quick-Measures-Gallery/Going-the-Distance/td-p/963267
Sure, what is the formula for that? Should be doable. I have one for distance. https://community.powerbi.com/t5/Quick-Measures-Gallery/Going-the-Distance/td-p/963267
OK, I modified the formula like this (below). Since you have this in Power Query, any chance you can share lat/long points and the bearings you are expecting? ATAN2 is included in the formula below, no reason to avoid it. https://community.powerbi.com/t5/Quick-Measures-Gallery/ATAN2/td-p/963263
Bearing (Degrees) =
VAR __FromCity = SELECTEDVALUE('From City'[City])
VAR __ToCity = SELECTEDVALUE('To City'[City])
VAR __FromLat = LOOKUPVALUE('Table'[Latitude],'Table'[City],__FromCity)
VAR __ToLat = LOOKUPVALUE('Table'[Latitude],'Table'[City],__ToCity)
VAR __FromLong = LOOKUPVALUE('Table'[Longitude],'Table'[City],__FromCity)
VAR __ToLong = LOOKUPVALUE('Table'[Longitude],'Table'[City],__ToCity)
VAR __distanceLong = RADIANS(__ToLong - __FromLong)
VAR __x = COS(RADIANS(__ToLat)) * SIN(__distanceLong)
VAR __y = COS(RADIANS(__FromLat)) * SIN(RADIANS(__ToLat)) - SIN(RADIANS(__FromLat)) * COS(RADIANS(__ToLat)) * COS(__distanceLong)
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()
)
RETURN
MOD(
DEGREES(__atan2) + 360,
360
)
Are you using this?
Bearing from point A to B, can be calculated as,
β = atan2(X,Y),
where, X and Y are two quantities and can be calculated as:
X = cos θb * sin ∆L
Y = cos θa * sin θb – sin θa * cos θb * cos ∆L
User | Count |
---|---|
77 | |
74 | |
63 | |
61 | |
45 |
User | Count |
---|---|
108 | |
102 | |
93 | |
83 | |
64 |