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!

### Like this:

Like Loading...

*Related*

cyrilbrdIs 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