Locating closest match based on Latitude & Longitude

0 votes
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 in Power BI by Upasana
• 8,530 points
122 views

1 answer to this question.

0 votes

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 = 
    MINX(Stadiums,
    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]),
              FILTER(Stadiums,
                  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 by Shubham
• 13,300 points

Related Questions In Power BI

0 votes
1 answer

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

answered Mar 1 in Power BI by Cherukuri
• 27,880 points
183 views
0 votes
1 answer
+1 vote
1 answer

Selecting based on filters

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

answered Aug 11 in Power BI by anonymous
• 27,880 points
40 views
0 votes
1 answer

Group by columns on Power BI

On power BI desktop, do the following Add ...READ MORE

answered Sep 25, 2018 in Power BI by Kalgi
• 41,460 points
61 views
+3 votes
2 answers

Combine tables in Power BI

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

answered Oct 5, 2018 in Power BI by lina
• 8,110 points

edited Oct 11, 2018 by Kalgi 186 views
0 votes
1 answer

Power Bi Dax Table

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

answered Oct 5, 2018 in Power BI by Kalgi
• 41,460 points
92 views
0 votes
2 answers

Power BI Dax Multiple IF AND Statements

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

answered May 22 in Power BI by VNK
8,145 views
0 votes
1 answer

Distinct count filtered by condition using Power BI Dax

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

answered Oct 5, 2018 in Power BI by Kalgi
• 41,460 points
7,289 views
0 votes
1 answer

Power BI : Time-based drill downs powered by Azure Data Warehouse

I don't really think you need to ...READ MORE

answered Oct 29, 2018 in Power BI by Shubham
• 13,300 points
77 views
0 votes
1 answer

Can I get rid of the grey border on the report visuals with power bi embedded?

You can do the following to make the ...READ MORE

answered Apr 10 in Power BI by Shubham
• 13,300 points
304 views