Reply
sph1nkz
Frequent Visitor
Partially syndicated - Outbound

Do not include blank values in the results of calculated columns

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:

 

sph1nkz_0-1665203158670.png

 

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

 

Thanks 

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

Syndicated - Outbound

Change the RETURN expression to:

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








Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

2 REPLIES 2
PaulDBrown
Community Champion
Community Champion

Syndicated - Outbound

Change the RETURN expression to:

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








Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Syndicated - Outbound

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

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)