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 420 views

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:

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.

• 13,380 points

How to create final table based on Joins of two tables in power BI?

Hi Nithin, It is easy and simple. 1. Go ...READ MORE

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

Combine tables in Power BI

You can also achieve this using a ...READ MORE

Power Bi Dax Table

You need to wrap the numbers in ...READ MORE

Power BI Dax Multiple IF AND Statements

HI I have Column Patient 12 13 14 15 18 IN Patient=12, 13 Out ...READ MORE

Distinct count filtered by condition using Power BI Dax

Try this, it should work: DistinctCountActiveMonths = CALCULATE( ...READ MORE