0 votes
1 view
in BI by (12k points)

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.

1 Answer

0 votes
by (34.4k points)
  • 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]))

...