Measuring Distance in Power BI Desktop

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

DistCalc1

Hope that you find this useful!

Advertisements

2 thoughts on “Measuring Distance in Power BI Desktop

  1. cyrilbrd

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

    Reply
  2. Pingback: Measuring Distance in Power BI Desktop | Excel For You | CompkSoft

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s