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,170 points
52 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
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
• 12,810 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
• 15,460 points
68 views
0 votes
1 answer
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
• 36,340 points
30 views
0 votes
1 answer

Display power bi report on my desktop application

Power BI has a new feature called ...READ MORE

answered Sep 27, 2018 in Power BI by Kalgi
• 36,340 points
24 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,100 points

edited Oct 11, 2018 by Kalgi 96 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
• 36,340 points
47 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
4,522 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
• 36,340 points
2,781 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
• 12,810 points
27 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
• 12,810 points
112 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.