Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Galleries
- Quick Measures Gallery
- Going the Distance

03-07-2020 11:43 AM - last edited 05-13-2020 00:06 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Going the Distance

03-07-2020
11:43 AM

Uses the Haversine formula to compute the distance between the latitudes and longitudes of two points.

```
c =
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 __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
```

c is the main measure and then you need to multiple this by the radius of the earth in either km, miles, etc.

Note, this does not account for the elliptical shape of the Earth so don't use it for anything besides estimation as you could be off a few miles over long distances.

eyJrIjoiNWYwYTBjZjEtZGIxNi00NWExLWI5MzMtNjJlZDg5MTA1ZDU2IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-13-2023
03:40 AM

Thank you @Greg_Deckler this is brilliant and works well. I have an issue where I am using "From" and "To" postcodes but in some cases I do not know the "To" postcode (so that field is blank). This is then giving me a ridiculously huge number. Do you have a suggestion for how I can get it to either give me a figure of 0 or say "unknown" if one of the postcodes is unknown? Any help would be much appreciated. I'm still new to DAX and can't figure out the best way to do this.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-13-2023
05:59 AM

@fran_parrett Couple thoughts, let's say that one of your postcodes is blank and that is a substitute for City in the example. You could do this:

```
c =
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 __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
VAR __Result = IF( __FromCity = BLANK() || __ToCity = BLANK(), 0, __c)
RETURN
__Result
```

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-13-2023
06:22 AM

@Greg_Deckler thank you so much. Yes the city was substituted for postcodes as I needed it on a much more local scale. This worked perfectly though! I really appreciate your help.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-07-2020
12:07 PM

@Greg_Deckler This is great, looking forward to seeing the final distance calculation.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-07-2020
12:40 PM

OK, I downloaded the UK Postal Code latitudes and longitudes from here:

https://www.freemaptools.com/download-uk-postcode-lat-lng.htm

You guys sure have a lot of postal codes, apparently about 1.7M of them and since they are unique as well as the latitudes and longitudes, well, it makes for a fairly sizeable file. Anyway, I went ahead and implemented a few columns and such to get the distances.

However, they only allow a maximum upload file size of 50MB and the file is nearly twice that. So I am uploading to my personal OneDrive and will share out a file from there. Hang tight.

Latest book!:

DAX is easy, CALCULATE makes DAX hard...