- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
Should not include the 0s in the results if either of the latitude or longitude is blank.
Thanks
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
03-11-2024 09:54 AM | |||
02-20-2024 11:31 AM | |||
06-11-2024 06:30 AM | |||
03-15-2024 05:37 AM | |||
Anonymous
| 07-08-2024 03:25 AM |
User | Count |
---|---|
126 | |
79 | |
60 | |
58 | |
44 |
User | Count |
---|---|
181 | |
121 | |
82 | |
70 | |
54 |