Geographical Distance Calculation in DAX using Haversine

Problem Analysis

The user wants to know whether the Haversine formula or the Bing Maps API is used to calculate distance in Power BI.

Strategic Solution Development

In Power BI, the calculation of distance between two points is not directly supported natively. However, you can use DAX expressions to implement the Haversine formula in Power BI or utilize the Bing Maps API.

Comprehensive Advice

To calculate distance using the Haversine formula, you can use DAX expressions which involve trigonometric functions. Here is a step-by-step approach to calculate the distance between two points using the Haversine formula in Power BI:

1. Ensure that you have latitude and longitude values for both points in your dataset. If not, you may need to extract or generate these values from your existing data.

2. Create a new measure or calculated column in Power BI using DAX.

3. Use the Haversine formula to calculate the distance between the two points. The formula requires the latitude and longitude values of the two points, and the Earth's radius. The formula is as follows:

   Distance = 
   VAR lat1 = RADIANS('Table'[Latitude1])
   VAR lon1 = RADIANS('Table'[Longitude1])
   VAR lat2 = RADIANS('Table'[Latitude2])
   VAR lon2 = RADIANS('Table'[Longitude2])
   VAR dlat = lat2 - lat1
   VAR dlon = lon2 - lon1
   VAR a =
       SIN(dlat/2) * SIN(dlat/2) +
       COS(lat1) * COS(lat2) *
       SIN(dlon/2) * SIN(dlon/2)
   VAR c = 2 * ATAN2(SQRT(a), SQRT(1-a))
   VAR distance = c * 6371 -- Earth radius in kilometers
   
   RETURN distance
   

   This DAX expression calculates the distance in kilometers. You can convert it to miles if needed.

4. Apply the formula to your dataset. The newly created measure or calculated column will display the distance between the two points.

Alternatively, you can use the Bing Maps API to calculate the distance between two points in Power BI. This approach involves utilizing the Web.Contents and Json.Document functions in Power Query to interact with the API and retrieve the distance data. Here are the high-level steps:

1. Register an account with Bing Maps and generate an API key.
2. Open Power Query Editor in Power BI.
3. Create a new function that takes the latitude and longitude values of two points as input parameters.
4. Inside the function, use the Web.Contents function to make a request to the Bing Maps API, passing the API key and the latitude and longitude values.
5. Extract the distance data from the response using the Json.Document function.
6. Apply the function to your dataset, passing the appropriate latitude and longitude values.
7. The function will return the distance between the two points, which can be used as a measure or calculated column in Power BI.

Detailed Explanation

Both the Haversine formula and the Bing Maps API can be used to calculate distance in Power BI.

The Haversine formula is a mathematical algorithm that calculates the distance between two points on a sphere, such as the Earth. It takes into account the curvature of the Earth's surface and provides an accurate estimate of the distance between two points given their latitude and longitude values. In Power BI, you can implement the Haversine formula using DAX expressions, making use of trigonometric functions such as SIN, COS, and ATAN2.

On the other hand, the Bing Maps API is an external service provided by Microsoft that offers a wide range of geographical functionalities, including distance calculation. To use the Bing Maps API in Power BI, you need to register an account, obtain an API key, and make requests to the API to retrieve distance data. This approach involves using the Web.Contents and Json.Document functions in Power Query to interact with the API.

The choice between the Haversine formula and the Bing Maps API depends on various factors, such as the level of accuracy required, the frequency of data updates, and the usability of external APIs in your organization. The Haversine formula is a good option if you have latitude and longitude values readily available in your dataset and need a simple and self-contained solution within Power BI. On the other hand, the Bing Maps API can provide more accurate results and may be suitable if you require up-to-date distance data or advanced geographical functionalities.

Overall, both options have their merits and limitations, and the choice depends on your specific requirements and preferences.

*Note: The code provided above is in DAX, the formula language used in Power BI.

Code Explanation

The provided code calculates the distance between two points on the Earth's surface using latitude and longitude coordinates.

1. It starts by declaring and initializing variables to store the latitude and longitude values of the two points:

• lat1 is assigned the value of the latitude of the first point from the 'Table' column 'Latitude1' in radians.
• lon1 is assigned the value of the longitude of the first point from the 'Table' column 'Longitude1' in radians.
• lat2 is assigned the value of the latitude of the second point from the 'Table' column 'Latitude2' in radians.
• lon2 is assigned the value of the longitude of the second point from the 'Table' column 'Longitude2' in radians.

2. The code then calculates the differences in latitude and longitude:

• dlat represents the difference in latitude between the two points (lat2 - lat1).
• dlon represents the difference in longitude between the two points (lon2 - lon1).

3. The code proceeds to calculate the distance between the two points using the Haversine formula, which is based on trigonometry.

• The formula calculates the "great circle distance" between two points on a sphere (in this case, the Earth).
• a is calculated as the sum of the following terms:
  • The square of the sine of half the latitude difference (SIN(dlat/2) * SIN(dlat/2)).
  • The product of the cosine of the first latitude, the cosine of the second latitude, and the square of the sine of half the longitude difference (COS(lat1) * COS(lat2) * SIN(dlon/2) * SIN(dlon/2)).
• c is calculated as the arctangent of the square root of a divided by the square root of (1 - a) (2 * ATAN2(SQRT(a), SQRT(1-a))).
• Finally, the distance variable is calculated as the product of c and the Earth's radius in kilometers (6371).

4. The calculated distance, stored in the distance variable, is then returned as the output of the calculation.

Key Concepts

• RADIANS function is used to convert degrees to radians, which is a widely used unit of measurement in trigonometry calculations.
• Haversine formula is used to calculate the distance between two points on a sphere, such as the Earth's surface.
• The formula takes into account the curvature of the Earth, making it more accurate for long distances compared to other distance calculation methods.

Additional Example

Here's an example of how the code can be used in practice:

Distance = 
VAR lat1 = RADIANS(37.7749) -- Latitude of point 1 in degrees
VAR lon1 = RADIANS(-122.4194) -- Longitude of point 1 in degrees
VAR lat2 = RADIANS(34.0522) -- Latitude of point 2 in degrees
VAR lon2 = RADIANS(-118.2437) -- Longitude of point 2 in degrees
VAR dlat = lat2 - lat1
VAR dlon = lon2 - lon1
VAR a =
    SIN(dlat/2) * SIN(dlat/2) +
    COS(lat1) * COS(lat2) *
    SIN(dlon/2) * SIN(dlon/2)
VAR c = 2 * ATAN2(SQRT(a), SQRT(1-a))
VAR distance = c * 6371 -- Earth radius in kilometers

RETURN distance

This example calculates the distance between San Francisco (latitude 37.7749, longitude -122.4194) and Los Angeles (latitude 34.0522, longitude -118.2437) and returns the result. The output would be the approximate distance between the two cities in kilometers.

The DAX code you presented calculates the geographical distance between two points, represented by their respective latitudes and longitudes, using the Haversine formula. Here's what each of the segments in your DAX formula does:

1. Radians Conversion: lat1, lat2, lon1 and lon2 convert the latitudes and longitudes from degrees to radians. DAX functions like SIN, COS etc. use radians as the unit.

2. Haversine Formula: dlat and dlon calculate the change in coordinates, while 'a' calculates half of the chord length between the points. 'c' is the angular distance in radians between the points, and 'distance' multiplies this angular distance by Earth's radius (in kilometers) to get the actual distance.

However, note that 'Table' should be replaced with the actual name of your table, if it isn't 'Table'.

Here is the formatted DAX query:

Distance = 
VAR lat1 = RADIANS('Table'[Latitude1])
VAR lon1 = RADIANS('Table'[Longitude1])
VAR lat2 = RADIANS('Table'[Latitude2])
VAR lon2 = RADIANS('Table'[Longitude2])
VAR dlat = lat2 - lat1
VAR dlon = lon2 - lon1
VAR a =
    SIN(dlat/2) * SIN(dlat/2) +
    COS(lat1) * COS(lat2) *
    SIN(dlon/2) * SIN(dlon/2)
VAR c = 2 * ATAN2(SQRT(a), SQRT(1-a))
VAR distance = c * 6371 -- Earth radius in kilometers
RETURN distance

By using the VAR keyword, you are creating intermediary steps, making your code more readable and debuggable, which is a best practice in DAX.

For more advanced learning, I recommend taking a closer look at the DAX language through the Enterprise DNA Platform's course content.

Language/Technology Identification

The provided code snippet is written in DAX (Data Analysis Expressions), a query language used primarily in Power BI, Microsoft Analysis Services, and the Power Pivot in Excel.

Original Code

Distance = 
VAR lat1 = RADIANS('Table'[Latitude1])
VAR lon1 = RADIANS('Table'[Longitude1])
VAR lat2 = RADIANS('Table'[Latitude2])
VAR lon2 = RADIANS('Table'[Longitude2])
VAR dlat = lat2 - lat1
VAR dlon = lon2 - lon1
VAR a =
    SIN(dlat/2) * SIN(dlat/2) +
    COS(lat1) * COS(lat2) *
    SIN(dlon/2) * SIN(dlon/2)
VAR c = 2 * ATAN2(SQRT(a), SQRT(1-a))
VAR distance = c * 6371 -- Earth radius in kilometers
RETURN distance

Code Refactoring

Refactoring is not required for this code. All the variables are introduced using the VAR statement, and their purposes are self-explanatory to those familiar with the Haversine formula for calculating distances between two points on a sphere from their longitudes and latitudes. However, a minor modification can be introduced to clarify the purpose of some of the numbers present.

Refactored Code:

Distance = 
VAR EarthRadiusInKm = 6371
VAR lat1 = RADIANS('Table'[Latitude1])
VAR lon1 = RADIANS('Table'[Longitude1])
VAR lat2 = RADIANS('Table'[Latitude2])
VAR lon2 = RADIANS('Table'[Longitude2])
VAR dlat = lat2 - lat1
VAR dlon = lon2 - lon1
VAR a =
    SIN(dlat/2) * SIN(dlat/2) +
    COS(lat1) * COS(lat2) *
    SIN(dlon/2) * SIN(dlon/2)
VAR c = 2 * ATAN2(SQRT(a), SQRT(1-a))
VAR distance = c * EarthRadiusInKm
RETURN distance

Documentation of Changes

In the refactored code:

1. I introduced an additional variable EarthRadiusInKm to replace the magic number 6371, which denotes the Earth's radius in kilometers. This modification enhances readability by spelling out what the magic number represents.

By explicitly stating the earth radius, it becomes easier to understand the intent of the formula, and easier for future reviewers or developers if they need to change the value or the unit of distance.

The core functionality of calculating the distance between two geographic coordinates remains the same, while the readability is improved. Overall, the code aligns well with the DAX best practices. More advanced DAX techniques like creating measures, handling BLANK values, and optimization, aren't needed for this specific snippet.

DAX RADIANS Function

The RADIANS function is a trigonometric function in DAX (Data Analysis Expressions). In a nutshell, it converts an angle, given in degrees, into radians.

How does it work?

The conversion from degrees to radians is relatively straightforward. Here's the formula used in the RADIANS function:

Radians = Degrees * PI/180

This is necessary because trigonometric functions such as SIN, COS, TAN, etc., in DAX expect their input in radians, not degrees. So, if you're dealing with angle measures in degrees, you would typically use the RADIANS function before using these trigonometric functions.

Example Usage

Let's say you have an angle of 45 degrees and you want to calculate its sine. First, convert the degrees to radians using the RADIANS function, and then pass the result to the SIN function.

SIN ( RADIANS ( 45 ) )

The function RADIANS ( 45 ) will convert 45 degrees into radians, and then SIN() will calculate the sine of this radian measure.

Important Note

Always remember that trigonometric functions like SIN, COS, TAN, etc., in DAX take their arguments in radians. This is a common source of confusion and mistakes, so always ensure to convert from degrees to radians using the RADIANS function when necessary.

To get a more in-depth understanding of using DAX, consider taking some of the relevant courses on the Enterprise DNA Platform. They are designed to equip you with the necessary knowledge and skills to work efficiently with DAX function in Power BI.