PowerBI大基数数据集DAX累计求和内存溢出问题及优化方案

初始问题与尝试 #

在包含30000个唯一值的数据集上实现DAX累计求和时，遇到性能瓶颈：几千个唯一值的小数据集可正常运行，但大数据集下耗时极长，甚至出现内存溢出。

初始使用的DAX逻辑如下：

atest2 = 

VAR selectedPosition = SELECTEDVALUE(ptSKUSummary[RankEq UOH2s])

VAR cumSumUOM = 
CALCULATE(
        SUM(ptSKUSummary[Eq UOH2s]),
        ptSKUSummary[RankEq UOH2s] <= selectedPosition,
        ALLSELECTED(ptSKUSummary[RankEq UOH2s])
)

RETURN cumSumUOM

完整背景补充 #

为终端用户构建报表应用，内置38个基于事实表可求和字段的基础度量值，用户可按需选择使用；数据源为标准化表结构的数据库，用户通过导入PowerBI模板（.pbit文件）生成各类报表。

核心问题是构建帕累托（80-20）图表：X轴为SKU占比，Y轴为销量占比。当前项目的事实表包含1300万条记录、30000个唯一SKU，若按单个SKU计算累计求和（X轴步长0.0033%），会运行20分钟后触发内存溢出。

最终解决方案 #

将30000个SKU划分为1000个逻辑桶（步长0.1%）后再执行累计求和计算，大幅降低计算量与内存占用。最终使用的DAX代码如下：

msPercUOMP4 = 

VAR selectedPercentile = SELECTEDVALUE(ptParetoPercentileP2[Percent of SKUs])
VAR numSKUs = CALCULATE( [msUniqueSKUs], ALLSELECTED(ptOrderFile) )
VAR totalVolume = CALCULATE( [msSumUOM], ALLSELECTED(ptOrderFile) )

VAR vtSKUsummary = 
SUMMARIZE(
    ALLSELECTED(ptOrderFile),
    ptOrderFile[SKU],
    "@Volume", [msSumUOM]
)

VAR vtSKUsummaryRanked = 
ADDCOLUMNS(
    vtSKUsummary,
    "@Rank", ROWNUMBER(vtSKUsummary, ORDERBY([@Volume], DESC, ptOrderFile[SKU], ASC))
)

VAR vtSKUpercentiles = 
ADDCOLUMNS(
    vtSKUsummaryRanked,
    "@Percentile", ROUND(DIVIDE([@Rank], numSKUs), 3)
)

VAR prevSelectedVolume =
SUMX(
    FILTER(vtSKUpercentiles, [@Percentile] <= selectedPercentile-0.001),
    [@Volume]
)

VAR selectedVolume = 
SUMX(
    FILTER(vtSKUpercentiles, [@Percentile] <= selectedPercentile),
    [@Volume]
)

VAR result = DIVIDE(selectedVolume, totalVolume, 0 )

RETURN 
IF( prevSelectedVolume = selectedVolume, BLANK(), IF(result = BLANK(), 0, result) )

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