Do not include blank values in the results of calculated columns

[sph1nkz]

Hi,

I create calculated column to compute distance between 2 locations.

End state would be:

1. Compute results excluding the location itself

2. Do not put any value on results if either of the value of latitude & longitude is blank.

Here is my code credits to @AIB:

Distance (KM) =
VAR Lat1 = facilities_tbl[Latitude]
VAR Lng1 = facilities_tbl[Longitude]
VAR name_ = facilities_tbl[Name of plant]
VAR P =
DIVIDE ( PI (), 180 )
RETURN
MINX (
FILTER ( facilities_tbl, facilities_tbl[Name of plant] <> name_ ),
VAR Lat2 = facilities_tbl[Latitude]
VAR Lng2 = facilities_tbl[Longitude]
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
)

Here's the result:

Should not include the 0s in the results if either of the latitude or longitude is blank.

Thanks 

[PaulDBrown · Accepted solution]

Change the RETURN expression to:

...
RETURN
IF (
    OR (
        ISBLANK ( facilities_tbl[Latitude] ),
        ISBLANK ( facilities_tbl[Longitude] )
    ),
    BLANK (),
    final
)

[sph1nkz]

It worked!!! Thank you so much @PaulDBrown. Really appreciate your help.