Power BI度量值加载过慢/失败，寻求性能优化方案

后端有数百万条数据，无任何筛选的单个可视化表格加载速度很快。为识别用户未填写的列并标注，编写了如下DAX度量值，但加入表格后加载时间极长，甚至偶尔加载失败：

C_Blank = 
SUMX(
    ADDCOLUMNS(
        SIT,
        "Count",
        var Direction = SIT[Direction]
        var INCO = SIT[INCOTERM]
        var res1 = COUNTROWS(
            FILTER(
                {SIT[Consol Number],SIT[ETD],SIT[ATD],SIT[ETA],SIT[Estimated Pickup],SIT[Interim Receipt Date],SIT[Actual Pickup]},
                NOT ISBLANK([Value])))
        var res2 = COUNTROWS(
            FILTER(
                {SIT[Consol Number],SIT[ETD],SIT[ATD],SIT[ETA],SIT[ATA],SIT[Estimated Pickup],SIT[Interim Receipt Date],
                 SIT[Actual Pickup],SIT[Estimated Delivery],SIT[Actual Delivery]},
                 NOT ISBLANK([Value])))
        var res3 = COUNTROWS(
            filter(
                {SIT[ATA],SIT[Estimated Delivery],SIT[Actual Delivery]},
                NOT ISBLANK([Value])))

        var res4 = COUNTROWS(
            FILTER({SIT[Estimated Pickup],SIT[Interim Receipt Date],SIT[Actual Pickup],SIT[Estimated Delivery],SIT[Actual Delivery]},
                  NOT ISBLANK([Value])))
         return  
        if (Direction = "Domestic" || SIT[Transport Mode] ="ROA", res4,
        if (Direction = "Export" && LEFT(INCO, 1) = "D" || (Direction = "Import" && LEFT(INCO, 1) = "D") , res2 , 
        if (Direction = "Export" && NOT(LEFT(INCO, 1) = "D") , res1,
        if (Direction = "Import" && NOT(LEFT(INCO, 1) = "D") , res3))))),
    [Count])

原度量值的性能瓶颈在于：逐行迭代时频繁创建临时表并调用COUNTROWS(FILTER(...))，对百万级数据而言内存和计算开销极大。以下是针对性优化：

核心优化点 #

• 替换低效计数逻辑：利用NOT ISBLANK()返回的布尔值可直接转为1/0的特性，用加法直接统计非空列数，避免创建临时表和过滤操作。
• 简化条件判断：将重复计算的INCO前缀判断提取为变量，把多层嵌套IF改为SWITCH函数，减少重复计算并提升逻辑可读性。
• 精简迭代结构：直接在SUMX中计算每行的非空列数，无需通过ADDCOLUMNS创建中间列，减少内存占用。

优化后的DAX代码 #

C_Blank = 
SUMX(
    SIT,
    VAR Direction = SIT[Direction]
    VAR TransportMode = SIT[Transport Mode]
    VAR INCO = SIT[INCOTERM]
    VAR IsINCO_D = LEFT(INCO, 1) = "D"
    -- 预定义各场景的非空列计数逻辑
    VAR Count1 = 
        NOT ISBLANK(SIT[Consol Number]) +
        NOT ISBLANK(SIT[ETD]) +
        NOT ISBLANK(SIT[ATD]) +
        NOT ISBLANK(SIT[ETA]) +
        NOT ISBLANK(SIT[Estimated Pickup]) +
        NOT ISBLANK(SIT[Interim Receipt Date]) +
        NOT ISBLANK(SIT[Actual Pickup])
    VAR Count2 = 
        Count1 +
        NOT ISBLANK(SIT[ATA]) +
        NOT ISBLANK(SIT[Estimated Delivery]) +
        NOT ISBLANK(SIT[Actual Delivery])
    VAR Count3 = 
        NOT ISBLANK(SIT[ATA]) +
        NOT ISBLANK(SIT[Estimated Delivery]) +
        NOT ISBLANK(SIT[Actual Delivery])
    VAR Count4 = 
        NOT ISBLANK(SIT[Estimated Pickup]) +
        NOT ISBLANK(SIT[Interim Receipt Date]) +
        NOT ISBLANK(SIT[Actual Pickup]) +
        NOT ISBLANK(SIT[Estimated Delivery]) +
        NOT ISBLANK(SIT[Actual Delivery])
    -- 按条件返回对应计数
    RETURN
        SWITCH(
            TRUE(),
            Direction = "Domestic" || TransportMode = "ROA", Count4,
            (Direction = "Export" || Direction = "Import") && IsINCO_D, Count2,
            Direction = "Export" && NOT IsINCO_D, Count1,
            Direction = "Import" && NOT IsINCO_D, Count3,
            0 -- 兜底默认值
        )
)

额外性能建议 #

• 如果Direction和Transport Mode是枚举值，将其设置为文本类型列并开启列存储优化，减少计算时的类型转换开销。
• 若业务允许，给可视化表格添加切片器筛选，缩小度量值的计算范围。

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