DAX查询性能优化求助：Power BI度量值执行缓慢问题

我在Power BI中使用的一段DAX度量值执行耗时较长，现寻求性能优化帮助。

原度量值代码如下：

MEASURE FACT_CONSOL_BALANCE_OL[Measure 4] =
    SWITCH (
        TRUE (),
        CONTAINS (
            DIM_ANALYTIC_STRUCT_ACCOUNT,
            DIM_ANALYTIC_STRUCT_ACCOUNT[STRUCTURE_NODE (groups)], "1 - CURRENT ASSETS"
        ), SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] ),
        CONTAINS (
            DIM_ANALYTIC_STRUCT_ACCOUNT,
            DIM_ANALYTIC_STRUCT_ACCOUNT[STRUCTURE_NODE (groups)], "2 - NON - CURRENT ASSETS"
        ), SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] ),
        SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] ) * -1
    )

DAX Studio中的性能结果截图：

优化方案 #

1. 替换CONTAINS为上下文变量判断

原代码中CONTAINS会扫描整个DIM_ANALYTIC_STRUCT_ACCOUNT表，数据量大时性能开销极大。改用SELECTEDVALUE获取当前上下文的分组值，同时将SUM(BALANCE)存入变量避免重复计算：

MEASURE FACT_CONSOL_BALANCE_OL[Measure 4] =
VAR TotalBalance = SUM(FACT_CONSOL_BALANCE_OL[BALANCE])
VAR CurrentGroup = SELECTEDVALUE(DIM_ANALYTIC_STRUCT_ACCOUNT[STRUCTURE_NODE (groups)])
RETURN
    SWITCH (
        CurrentGroup,
        "1 - CURRENT ASSETS", TotalBalance,
        "2 - NON - CURRENT ASSETS", TotalBalance,
        TotalBalance * -1
    )

2. 预计算分组标识（性能提升最显著）

在DIM_ANALYTIC_STRUCT_ACCOUNT表中新增计算列，提前完成分组判断，把逻辑转移到数据刷新阶段：

BalanceMultiplier = 
IF (
    [STRUCTURE_NODE (groups)] IN {"1 - CURRENT ASSETS", "2 - NON - CURRENT ASSETS"},
    1,
    -1
)

之后度量值可简化为：

MEASURE FACT_CONSOL_BALANCE_OL[Measure 4] =
SUM(FACT_CONSOL_BALANCE_OL[BALANCE]) * SELECTEDVALUE(DIM_ANALYTIC_STRUCT_ACCOUNT[BalanceMultiplier], -1)

3. 模型层面优化

• 确认DIM_ANALYTIC_STRUCT_ACCOUNT[STRUCTURE_NODE (groups)]列已启用列存储索引（Power BI默认开启，可在表设计中检查）
• 保证维度表与事实表的关系为单向筛选（仅维度表筛选事实表），避免不必要的上下文计算

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