- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations
- Power Platform and Dynamics 365 Integrations
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

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

Showing results for

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

- Power BI forums
- 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

05-31-2023
11:23 PM

@Greg_Deckler What do you mean by - c is the main measure and then you need to multiple this by the radius of the earth in either km, miles, etc.????

Isn't this the complete solution or there is something we have to multiply at the end after this whole piece of code?

Also for me, it shows '0' in the distance calculation.

Note- i have only 1 table having both to & from locations in the same table and I am using 'SELECTEDVALUE' intead of 'LOOKUP' for column reference still it gives me 0.

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

06-01-2023
05:44 AM

@jaisveer21 Download the PBIX associated with this post and you will see how to use the measure. I made it generic so that you could use it for either miles or kilometers.

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