Please contact me with any Excel Project work you may have. Written 10/28/2020
Email address: dhExcel1@gmail.com
Among the many new BI features introduced with the recent release of the free version of the Microsoft Power BI desktop is the “new” trigonometric DAX functions such as SIN and COS. Of course, these functions have been around forever in Excel, but for some reason were not included in the first two major releases of Power Pivot. So, now they are available to the DAX language (and also in Excel 2016, if you have a spare computer handy to use in the preview version), and as such can be used to calculate straight-line distances between two points on the Earth as defined by their latitude and longitude coordinates. Also, in the formula, the constant PI()/180 is required a number of times, so the implementation of the new DAX variable comes in very handy here. In Power BI, if you add a new measure named PI_D180 as equal to PI()/180 (0.0174532925199433), you can use that measure in your DAX formula for distance, as shown below.
Distance = ACOS(SIN(Distance[Latitude_1]*[PI_DIV180])*SIN(Distance[Latitude_2]*[PI_DIV180])+COS(Distance[Latitude_1]*[PI_DIV180])*COS(Distance[Latitude_2]*[PI_DIV180])*COS((Distance[Longitude_2]*[PI_DIV180])-(Distance[Longitude_1]*[PI_DIV180])))*3959
Hope that you find this useful!
Is this giving you a distance in Miles?
You could also use for the distance in Km:
New measure [RadiusEarth] = 6371
And New Column
Distance_km = [RadiusEarth]*((2*ASIN(SQRT((SIN((RADIANS(Distance[Lat_1])-RADIANS(Distance[Lat_2]))/2)^2)+COS(RADIANS(Distance[Lat_1]))*COS(RADIANS(Distance[Lat_2]))*(SIN((RADIANS(Distance[Long_1])-RADIANS(Distance[Long_2]))/2)^2)))))
Pingback: Measuring Distance in Power BI Desktop | Excel For You | CompkSoft
Pingback: #Excel Super Links #90 (Special Edition) – shared by David Hager | Excel For You
Pingback: Display Points within a Distance Radius on a Power BI Map - DataVeld
Pingback: Display Points within a Distance Radius on a Power BI Map – Learn Power BI