Distance DAX

[Anonymous]

Hi Everyone,

I am trying to make graph on power BI by using Longtitude and Latitude information. I want to know how can I use DAX funtion in power BI to get the distance in miles from one point to anther Point.

I am attached the map graph which I just created on Power BI.

Thanks

[fhill]

I had to do this recently between zip codes in Excel.  You have to convert Lat & Lon cordinates into Radiant values first, then you can use the formula in the 2nd screen shot.  DAX has a RADIANS, SIN & COS calcuation just like Excel.

The hard part may be how to display this information?  You'll need 2 sets of Lat & Lon to perform the calcuation, and I don't know how you plan to 'match up' two points to do so?

[Anonymous]

Return

I have one set of Latitude and Longtitude only. Can you resend the 3rd screen short little bigger?

Thanks

[fhill]

=IF(SIN(D2) * SIN(D3) + COS(D2) * COS(D3) * COS(F2 - F3) > 1,
3963.1 * ACOS(1), 3963.1 *
ACOS(SIN(D2) * SIN(D3) + COS(D2) * COS(D3) * COS(F2-F3)))

[Anonymous]

Hi Fhill,

I have the same kind of data which you have. My question is when I check the distance on google map, it is showing me that 2.2 miles but my calculation showing me 1.99 miles. These diffrence are showing me all data. I am not sure why Radiants formula result have 1.99 miles and google have 2.20 miles.

Here is the screen shot I have.

Thanks

[Anonymous]

I think, I get you wrong. So I have fifty rows of Latitude and 50 row of Longtitude.

When I use the formula you shared. I got 9.2152. My Lat is 43.72 and long is -79.7.

When I pug this lat and long into google map it is showing me 10.1 miles. Do you belive is there any problem?

Thanks

Amjad

[richbenmintz]

Hi @Anonymous ,

can you provide a representative dataset?

[fhill · Accepted solution]

Are you looking for the distance of Every (all 50) points FROM the same Lat 43.72 and long -79.7 every time?

What was the first example Lat/Lon you used in testing to get 9.2 vs. 10.1?  (Assuming I have 43.72 x -79.7 as the starting point)

FOrrest

[Anonymous]

Hi Fhill,

Sorry for late reply, I was away. So here is the detail.

I want to know all 50 lat and longtitude distance in miles from one location to see which one is best or near. 

for example, 43.42-80.70 to 43.78-79.55 and so on.

Thanks

[Anonymous]

Hi,

You used this formula to calculate miles ? If yes than you have one set of latitude and longtitude so how you calculate miles?

Thanks

[richbenmintz]

Hi @Anonymous ,

please see the blog post below, shout out to radacad,com

https://radacad.com/dynamic-distances-in-power-bi

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Anonymous]

I checked that already but not what I am looking for.

Thanks

[AlB]

Hi @Anonymous 

What  is the formula to get the distance based on Lat and long? Once you have it, the calculation should not be difficult.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi,

I do not have formula for that, I have data which have lon and lat.