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, 2019 in Power BI by Upasana
• 8,570 points
258 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, 2019 by Shubham
• 13,370 points

Related Questions In Power BI

0 votes
1 answer

How to show visualization (map) based on latitude and longitude points in power BI?

Follow the below steps: 1. Select the map ...READ MORE

answered Oct 17, 2019 in Power BI by anonymous
• 3,380 points
82 views
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, 2019 in Power BI by Cherukuri
• 32,420 points
697 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, 2019 in Power BI by anonymous
• 32,420 points
65 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,180 points

edited Oct 11, 2018 by Kalgi 273 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
• 49,340 points
134 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, 2019 in Power BI by VNK
10,379 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
• 49,340 points
10,825 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,370 points
86 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, 2019 in Power BI by Shubham
• 13,370 points
449 views