4

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.

jonas9495
  • 43
  • 1
  • 4
  • I would just find the minimum distance between the tracked position and the stadium location, try the haversine fucntion, you may be able to get away with simple euclidean distance. You are probably going to want to prep the data somewhere else and bring it back into power bi once its ready. – StelioK Jun 08 '18 at 15:04

1 Answers1

7

Here's one possible approach:

First, calculate the minimal distance using the Haversine function.

Add 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]))

Note: I rounded the values to avoid not matching from possible floating point errors. This may or may not be necessary.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Very nice solution! – StelioK Jun 08 '18 at 16:08
  • Thank you Alexis. Which format do I need the coordinates in, to subtract the coordinates from each other. Right now I got it as text (34.056687222, -117.195731667). Is there any possibility to transform them back into that format after the calculation? – jonas9495 Jun 11 '18 at 11:58
  • You need them as decimal number format. You can extract a number from a string using VALUE and turn a number to a string using FORMAT. – Alexis Olson Jun 11 '18 at 13:49