Tableau计算转Power BI DAX度量：无报错但结果异常求修正

原Tableau计算代码 #

IF [wlb_metric_name]='Count at Shift End'
AND DATE([Interval_end_Datetime_ET]) = [Shift End Time]
AND DATE([Shift Start Datetime])= [Overnight Shift Start Date]
AND [Date] = DATE([Interval_Start_Datetime_ET])
AND [Date] = [Shift End Time]
THEN [wlb_metric_value] ELSE 0 END
)/
COUNT(
IF [wlb_metric_name]='Count at Shift End'
AND DATE([Interval_end_Datetime_ET]) = [Shift End Time]
AND DATE([Shift Start Datetime])= [Overnight Shift Start Date]
AND [Date] = DATE([Interval_Start_Datetime_ET])
AND [Date] = [Shift End Time]
THEN [wlb_metric_value] ELSE NULL END
),0) 

尝试的DAX度量（结果异常） #

IF(
    ISBLANK(SUMX(
        FILTER(
            'Workload Balancing Fact',
            [wlb_metric_name] = "Count at Shift End"
                && [Interval_end_Datetime_ET].[Date] = [Shift End Time]
                && [Shift Start Datetime].[Date] = [Overnight Shift Start Date]
                && [Date] = [Interval_Start_Datetime_ET].[Date]
                && [Date] = [Overnight Shift Start Date].[Date]
        ),
        [wlb_metric_value]
    ) / COUNTX(
        FILTER(
            'Workload Balancing Fact',
            [wlb_metric_name] = "Count at Shift End"
                && [Interval_end_Datetime_ET].[Date] = [Shift End Time]
                && [Shift Start Datetime].[Date] = [Overnight Shift Start Date]
                && [Date] = [Interval_Start_Datetime_ET].[Date]
                && [Date] = [Overnight Shift Start Date]
        ),
        [wlb_metric_value]
    )),
    0,
    SUMX(
        FILTER(
            'Workload Balancing Fact',
            [wlb_metric_name] = "Count at Shift End"
                && [Interval_end_Datetime_ET].[Date] = [Shift End Time]
                && [Shift Start Datetime].[Date] = [Overnight Shift Start Date]
                && [Date] = [Interval_Start_Datetime_ET].[Date]
                && [Date] = [Overnight Shift Start Date]
        ),
        [wlb_metric_value]
    ) / COUNTX(
        FILTER(
            'Workload Balancing Fact',
            [wlb_metric_name] = "Count at Shift End"
                && [Interval_end_Datetime_ET].[Date] = [Shift End Time]
                && [Shift Start Datetime].[Date] = [Overnight Shift Start Date]
                && [Date] = [Interval_Start_Datetime_ET].[Date]
                && [Date] = [Shift End Time]
        ),
        [wlb_metric_value]
    )
)

错误排查 #

• 筛选条件不一致：原Tableau最后一个筛选条件是[Date] = [Shift End Time]，但你写的DAX里多次出现[Date] = [Overnight Shift Start Date].[Date]或[Date] = [Overnight Shift Start Date]，条件不匹配导致筛选范围错误，这是结果异常的核心原因。
• 代码冗余易出错：重复编写相同的FILTER逻辑，不仅冗余，还容易在修改时出现条件不一致的问题。
• 除数为零处理复杂：用ISBLANK判断再重复计算，不如DAX原生的DIVIDE函数简洁，DIVIDE可自动处理除数为零的情况，直接返回指定默认值。
• 日期比较语法不严谨：使用[列名].[Date]依赖自动生成的日期层级，若字段类型不一致可能导致比较失效，建议用DATE()函数显式提取日期部分。

正确DAX度量写法 #

Shift End Average = 
VAR FilteredTable = 
    FILTER(
        'Workload Balancing Fact',
        [wlb_metric_name] = "Count at Shift End"
            && DATE([Interval_end_Datetime_ET]) = [Shift End Time]
            && DATE([Shift Start Datetime]) = [Overnight Shift Start Date]
            && [Date] = DATE([Interval_Start_Datetime_ET])
            && [Date] = [Shift End Time]
    )
RETURN
    DIVIDE(
        SUMX(FilteredTable, [wlb_metric_value]),
        COUNTX(FilteredTable, [wlb_metric_value]),
        0
    )

说明 #

• 用VAR定义筛选后的表，避免重复计算，同时保证筛选条件完全匹配原Tableau逻辑。
• DATE()函数显式提取日期时间字段的日期部分，确保日期比较的准确性。
• DIVIDE函数完成除法运算，第三个参数设置为0，当除数为零时自动返回0，替代原Tableau的ZN逻辑，代码更简洁可靠。

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