topic Re: Going the Distance in Quick Measures Gallery

Going the Distance

Greg_Deckler — Wed, 13 May 2020 07:06:01 GMT

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

Re: Going the Distance

MYDATASTORY — Sat, 07 Mar 2020 20:07:17 GMT

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

Re: Going the Distance

Greg_Deckler — Sat, 07 Mar 2020 20:40:29 GMT

MYDATASTORY

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.

Re: Going the Distance

alissa — Wed, 08 Sep 2021 16:52:15 GMT

Thank you, Greg_Deckler !! This worked perfectly for me. Much appriciated!

Re: Going the Distance

fran_parrett — Fri, 13 Jan 2023 11:40:39 GMT

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.

Re: Going the Distance

Greg_Deckler — Fri, 13 Jan 2023 13:59:18 GMT

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:

Re: Going the Distance

fran_parrett — Fri, 13 Jan 2023 14:22:56 GMT

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.

Re: Going the Distance

jaisveer21 — Thu, 01 Jun 2023 06:23:40 GMT

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.

Re: Going the Distance

Greg_Deckler — Thu, 01 Jun 2023 12:44:38 GMT

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.