PowerPivot实现两点经纬度距离计算函数报错问题咨询

核心报错原因 #

你原来的公式直接套用了Excel工作表函数语法，忽略了两个核心问题：

1. 浮点计算精度问题：经纬度计算后传入ACOS的参数偶尔会超出[-1,1]的合法取值范围，直接触发错误
2. 语法差异：如果是在Power Query中计算，Power Query的数值函数前缀带Number.标识，和Excel/DAX函数名不通用

1. PowerPivot（DAX）计算列实现（400万行性能最优） #

直接新建计算列，使用以下DAX公式即可：

= 
VAR __acosInput = 
    COS(RADIANS(90 - 'Origin vs Destination w PKG'[Latitude])) 
    * COS(RADIANS(90 - 'Origin vs Destination w PKG'[DLatitude])) 
    + SIN(RADIANS(90 - 'Origin vs Destination w PKG'[Latitude])) 
    * SIN(RADIANS(90 - 'Origin vs Destination w PKG'[DLatitude])) 
    * COS(RADIANS('Origin vs Destination w PKG'[Longitude] - 'Origin vs Destination w PKG'[DLongitude]))
// 钳位处理规避浮点精度导致的参数越界
VAR __validAcosInput = MAX(MIN(__acosInput, 1), -1)
RETURN
ACOS(__validAcosInput) * 3958.8

2. 如果你需要在Power Query中计算的修正方案 #

新建自定义列，使用以下M语言公式：

let
    lat1 = [Latitude],
    lon1 = [Longitude],
    lat2 = [DLatitude],
    lon2 = [DLongitude],
    acosInput = Number.Cos(Number.Radians(90 - lat1)) * Number.Cos(Number.Radians(90 - lat2)) + Number.Sin(Number.Radians(90 - lat1)) * Number.Sin(Number.Radians(90 - lat2)) * Number.Cos(Number.Radians(lon1 - lon2)),
    validAcosInput = List.Max({List.Min({acosInput, 1}), -1})
in
    Number.Acos(validAcosInput) * 3958.8

通用注意事项 #

• 计算前请确认Latitude/DLatitude/Longitude/DLongitude四个字段的数据类型为十进制数字，无空值、非数值类无效内容
• 公式中3958.8为地球英里半径，如需返回公里单位的距离，替换为6371即可

内容的提问来源于stack exchange，提问作者Sebastian