Power BI - Find closest location based on Lat/Lng

Question

I am new to Power BI and DAX, so I hope you can help me.

I have two tables without any relationship: Table A contains lat/lng and date of tracked positions.Table B contains lat/lng and names of all stadiums.

I want to find the closest stadium near the tracked position. Also if possible I want to validate, if the position was in a specific radius of that stadium.

Any help greatly appreciated.

Answer 1

• First, calculate the minimal distance using the Haversine function.

• It needs to be added as this 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.

• We can now match up distances to 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]))

I would recommend this Power BI Microsoft certification course online by Intellipaat.