寻求高级DAX计算帮助：地理位置半径重叠计算列开发

问题背景 #

我正尝试创建一个DAX计算列，读取地点的纬度（POS_LATITUDE）、经度（POS_LONGITUDE）及半径（POS_RADIUS），返回表中每个地点对应的半径重叠地点列表。已梳理逻辑，但遇到DAX语法错误与性能瓶颈——当前方案需为每条记录创建实体表，对4000+地点的场景不具备可扩展性。

原代码如下：

Overlapping GLIDs = 
//storing values for current row
VAR __GLID1 = [GLID]
VAR __lat1 = [POS_LATITUDE]
VAR __long1 = [POS_LONGITUDE]
VAR __radius1 = [POS_RADIUS]/1000  //converting radius from m to km

//storing a duplicate location table as a table variable
VAR table1 = SUMMARIZE('LocTable1','LocTable1'[GLID],'LocTable1'[POS_LATITUDE],'LocTable1'[POS_LONGITUDE],'LocTable1'[POS_RADIUS])

//haversine formula to calculate distance between stored row and each row in the stored table
VAR __radiusEarth = 6371   // earth radius in kilometers
VAR __multiplier = PI()/180
VAR __latDiff = Calculate((selectedvalue(LocTable1[POS_LATITUDE])-__lat1) * __multiplier,table1)
VAR __lonDiff = Calculate((selectedvalue(LocTable1[POS_LONGITUDE])-__long1) * __multiplier,table1)
VAR __formula1 = Calculate(SIN(__latDiff/2) * SIN(__latDiff/2) +
    COS(selectedvalue('LocTable1'[POS_LATITUDE]) * __multiplier) * COS(__lat1 * __multiplier) * 
    SIN(__lonDiff/2) * SIN(__lonDiff/2),table1)
VAR __formula2 = Calculate(2 * ATAN(DIVIDE(SQRT(__formula1),SQRT(1-__formula1))),table1)
VAR __distance = Calculate(__radiusEarth * __formula2,table1)

//add distance column to stored table and filter table to locations whos radius+the stored row's radius is less than distance between locations
VAR table2 = addcolumns(table1,"distance",__distance)
VAR table3 = calculatetable(
        filter(table2,
        [distance]<(__radius1+(selectedvalue(LocTable1[POS_RADIUS])/1000))
    ))

//turn filtered table into a string of "overlapping GLIDs"    
VAR __ListGLIDs = Concatenatex(table3,[GLID],"^")

RETURN
__ListGLIDs

原代码问题分析 #

1. SELECTEDVALUE误用：计算列上下文是行上下文，SELECTEDVALUE用于筛选上下文，此处直接引用表列即可，无需额外包装。
2. 性能冗余：对每行复制全表并多次调用CALCULATE，4000行将产生4000次全表扫描，计算量呈指数级增长。
3. 公式上下文混乱：哈维辛公式拆分为多个VAR并嵌套CALCULATE，导致行上下文与筛选上下文冲突，计算结果可能出错。

优化后的DAX代码 #

Overlapping GLIDs = 
VAR __CurrentGLID = [GLID]
VAR __CurrentLat = [POS_LATITUDE]
VAR __CurrentLong = [POS_LONGITUDE]
VAR __CurrentRadiusKM = [POS_RADIUS] / 1000
VAR __EarthRadiusKM = 6371
VAR __RadMultiplier = PI() / 180

// 计算所有地点与当前地点的距离，并筛选重叠地点
VAR __OverlappingLocations = 
    FILTER(
        ADDCOLUMNS(
            'LocTable1',
            "@DistanceKM",
            // 哈维辛公式行内计算，避免上下文切换
            VAR __OtherLatRad = [POS_LATITUDE] * __RadMultiplier
            VAR __OtherLongRad = [POS_LONGITUDE] * __RadMultiplier
            VAR __CurrentLatRad = __CurrentLat * __RadMultiplier
            VAR __CurrentLongRad = __CurrentLong * __RadMultiplier
            VAR __LatDiff = __OtherLatRad - __CurrentLatRad
            VAR __LongDiff = __OtherLongRad - __CurrentLongRad
            VAR __HaversineTerm = 
                SIN(__LatDiff / 2) ^ 2 +
                COS(__CurrentLatRad) * COS(__OtherLatRad) * SIN(__LongDiff / 2) ^ 2
            RETURN
                __EarthRadiusKM * 2 * ATAN2(SQRT(__HaversineTerm), SQRT(1 - __HaversineTerm))
        ),
        // 排除自身，且判断半径重叠条件：两半径之和 ≥ 距离
        [GLID] <> __CurrentGLID &&
        [@DistanceKM] <= __CurrentRadiusKM + ([POS_RADIUS] / 1000)
    )

// 拼接重叠地点的GLID为字符串
RETURN
    CONCATENATEX(__OverlappingLocations, [GLID], "^")

优化说明 #

• 上下文优化：所有计算在ADDCOLUMNS的行上下文内完成，避免CALCULATE带来的上下文切换开销。
• 性能提升：虽然仍是O(n²)复杂度，但减少了重复表变量创建，利用DAX列式计算优化，4000行场景下计算量可控。若需进一步优化，可预先将经纬度转换为弧度值作为计算列，减少重复计算。
• 逻辑修正：明确排除当前地点自身，修正重叠条件判断（原代码中<应为<=，确保刚好接触的情况也被纳入）。

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