Finding distance between 2 points on the same table with additional criteria.

[vicpinto]

Hello all,

I have a table called SECTORS_4G_ENM with LAT and LON as well as some additional information like this:

Now I can calculate the shortest distance between any of these points using a formula I picked up from this forum. The formula is below and has been modified to work with this table:

Shortest Distance Type=

    VAR __baseLat = SECTORS_4G_ENM[LAT]

    VAR __baseLng = SECTORS_4G_ENM[LON]

    VAR __goldname = SECTORS_4G_ENM[GOLD_NAME]

    VAR P = DIVIDE ( PI(), 180 )

    VAR __distance =

    ADDCOLUMNS (

    FILTER(ALLSELECTED ( SECTORS_4G_ENM ),SECTORS_4G_ENM[GOLD_NAME]<>__goldname),

    "Distance",

        VAR Lat1 = __baseLat

        VAR Lng1 = __baseLng

        VAR Lat2 = SECTORS_4G_ENM[LAT]

        VAR Lng2 = SECTORS_4G_ENM[LON]

        VAR A = 0.5 - COS ( ( Lat2-Lat1 ) * P ) /2 +

            COS ( Lat1 * P ) * COS ( lat2 * P ) * ( 1 - COS ( ( Lng2- Lng1 ) * P ) ) / 2

        VAR final = 12742 * ASIN ( ( SQRT ( A ) ) )

        RETURN final

    )

    VAR minVal_ = MINX ( __distance, [Distance] )

RETURN

MINX ( FILTER ( __distance, [Distance] = minVal_), SECTORS_4G_ENM[SITE_TYPE] )

However, this returns the shortest distance to any SITE_TYPE - Big or Small. This is not what i want to acomplish.

What I want to acomplish is the for every GOLD_NAME, regardless of whether it is Big or Small type, return the shortest distance to the nearest SITE_TYPE that is Big.

Thanks in advance.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.