优化高内存占用的Calculated Column：解决支付记录排名内存溢出问题

首先得说，你用计算列+RANKX处理1000万行数据的思路，本质上踩了DAX计算列的性能大坑——计算列是模型处理时预计算所有行，每一行都要对全表做一次过滤扫描，1000万行就等于1000万次全表遍历，内存不炸才怪。结合你SSAS表分区、仅增量处理的场景，给你几个可行的替代方案：

方案一：用度量值替代计算列（优先推荐） #

度量值是查询时按需计算，不会预先占用内存，而且能利用SSAS的聚合优化和分区扫描特性，完美适配你的增量处理需求。

以下是适配你需求的DAX度量值：

Group Rank = 
VAR CurrentPerson = SELECTEDVALUE('Payment'[Person_Ref])
VAR CurrentElement = SELECTEDVALUE('Payment'[ElementId])
VAR CurrentEndDate = SELECTEDVALUE('Payment'[Pay End Date])
-- 提取当前分组的所有EndDate并转换（NULL视为2999-01-01）
VAR GroupedDates = 
    CALCULATETABLE(
        ADDCOLUMNS(
            VALUES('Payment'[Pay End Date]),
            "@AdjustedDate", IF(ISBLANK([Pay End Date]), DATE(2999,1,1), [Pay End Date])
        ),
        'Payment'[Person_Ref] = CurrentPerson,
        'Payment'[ElementId] = CurrentElement
    )
-- 对转换后的日期排序
VAR SortedDates = SORT(GroupedDates, [@AdjustedDate], ASC)
-- 匹配当前行的日期在排序后的位置
VAR CurrentAdjustedDate = IF(ISBLANK(CurrentEndDate), DATE(2999,1,1), CurrentEndDate)
RETURN
    XMATCH(CurrentAdjustedDate, SELECTCOLUMNS(SortedDates, "@Date", [@AdjustedDate]),,ASC)

为什么这个方案适合你？ #

• 只有当用户查询特定PersonRef/ElementId时才计算排名，不会预计算所有1000万行；
• SSAS会自动扫描相关分区（而不是全表），新增排名1的记录时，仅处理对应分区，不会触发全量刷新；
• 用CALCULATETABLE+VALUES替代原方案的FILTER，大幅减少扫描的数据量，性能提升明显。

方案二：优化计算列（如果必须用计算列） #

如果业务上必须用计算列，那得重构DAX逻辑，减少全表扫描的开销。核心是用ALLEXCEPT替代FILTER，因为ALLEXCEPT是基于列上下文的筛选，比逐行过滤高效得多：

Rank Calculated Column = 
VAR CurrentPerson = 'Payment'[Person_Ref]
VAR CurrentElement = 'Payment'[ElementId]
VAR CurrentAdjustedDate = IF(ISBLANK('Payment'[Pay End Date]), DATE(2999,1,1), 'Payment'[Pay End Date])
RETURN
    RANKX(
        CALCULATETABLE(
            'Payment',
            ALLEXCEPT('Payment', 'Payment'[Person_Ref], 'Payment'[ElementId])
        ),
        IF(ISBLANK('Payment'[Pay End Date]), DATE(2999,1,1), 'Payment'[Pay End Date]),
        CurrentAdjustedDate,
        ASC,
        DENSE
    )

注意事项 #

即使优化后，计算列依然会在模型处理时预计算所有行，内存占用还是会很高——如果你的SSAS服务器内存不足以承载1000万行的计算列数据，还是优先选方案一。

方案三：分区内预计算（需结合分区策略） #

如果你的SSAS分区是按PersonRef或能保证同一分组（PersonRef+ElementId）落在单个分区的键划分，那可以在分区处理时，用Power Query对每个分区内的数据预先计算排名。比如在Power Query中：

1. 按PersonRef和ElementId分组；
2. 对每个组内的Pay End Date排序（NULL排最后）；
3. 添加排名列。

但这个方案的前提是分区键能覆盖分组逻辑——如果你的分区是按时间划分，同一PersonRef的记录可能跨多个分区，那这个方案就不适用（会导致排名计算不完整）。

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