So, I have, on Power BI, 2 tables with no relationship. One table contains Latitude & Longitude, along with dates, of positions tracked. And the other table contains the same coordinates but with names of Stadiums.

What I want to do is find the closest stadium near the tracked position.

Can anyone help me out? Thanks in advance! Jan 2, 2019 in Power BI 1,605 views

## 1 answer to this question.

First of all, we need to use the Haversine function to calculate the minimal distance.

This is to be added as a calculated column to your tracked table.

```Nearest =
ROUND(2 * 3959 *
ASIN(SQRT(
SIN((Stadiums[Lat] - Tracked[Lat]) * PI()/360)^2 +
COS(Tracked[Lat] * PI()/180) * COS(Stadiums[Lat] * PI()/180) *
SIN((Stadiums[Lon] - Tracked[Lon]) * PI()/360)^2)), 1))
```

In this formula, 3959 is the radius of the Earth in miles.

Now, to match up distances & find the nearest stadium:

```Stadium = CALCULATE(MAX(Stadiums[Stadium]),
ROUND(2 * 3959 *
ASIN(SQRT(
SIN((Stadiums[Lat] - Tracked[Lat]) * PI()/360)^2 +
COS(Tracked[Lat] * PI()/180) * COS(Stadiums[Lat] * PI()/180) *
SIN((Stadiums[Lon] - Tracked[Lon]) * PI()/360)^2)), 1)
= Tracked[Nearest]))
```

PS: The values are rounded to avoid not matching from possible floating point errors. It's not really a necessary step. answered Jan 2, 2019 by
• 13,480 points

## How do I label bar graph with different colors based on values from different slicers?

If the user selects only one value ...READ MORE

+1 vote

## Selecting based on filters

Hi,  If you have more than one table ...READ MORE

## DAX EARLIER() function in Power Query

Solutions in the code below. Notice that ...READ MORE

## DAX / PowerPivot query functions to spread aggregated values over time period

The solution below assumes a table called ...READ MORE

## Excel PowerPivot DAX Calculated Field

You should create a relationship between each ...READ MORE

## PowerPivot DAX - Dynamic Ranking Per Group (Min Per Group)

Firstly create a basic measure [Amount] to sum the ...READ MORE