Power BI - Find closest location based on Lat/Lng

2019-07-02 01:57发布

问题:

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:

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.