DAX度量异常：Total Hours Worked2计算不符合预期问题排查

需要在可视化组件中实现Total Hours Worked列，当StartTime与EndTime相同时显示0（代表驾驶员未退出应用，不应计24小时）。当前使用以下DAX度量，但Total Hours Worked2未正常工作：条目4起止时间不匹配，却仍返回0。

现有DAX代码 #

Hours Worked #

Hours Worked = 

VAR earlieststart = CALCULATE(MIN('HOS Logs 1 0'[StartTime]),'HOS Logs 1 0'[Status]<>"offDuty")

VAR latestworktime = CALCULATE(MAX('HOS Logs 1 0'[EndTime]),'HOS Logs 1 0'[Status]<>"offDuty")

VAR timeworked = DATEDIFF(earlieststart,latestworktime,HOUR)

RETURN

IF(MIN('HOS Logs 1 0'[StartTime]) = MAX('HOS Logs 1 0'[EndTime]),0,timeworked)

Total Hours Worked #

Total Hours Worked = SUMX('HOS Logs 1 0',[Hours Worked])  

Total Hours Worked2 #

Total Hours Worked2 = SUMX('HOS Daily Logs 1 0',IF('HOS Daily Logs 1 0'[StartTime] = 'HOS Daily Logs 1 0'[EndTime], 0, [Total Hours Worked]))

问题分析 #

Total Hours Worked2的核心问题是上下文传递错误：在迭代HOS Daily Logs 1 0表时，调用的[Total Hours Worked]是基于HOS Logs 1 0表的全局聚合度量，没有限定到当前日记录对应的驾驶员、日期等范围，导致即使当前行起止时间不匹配，聚合结果仍可能为0。

同时原Hours Worked度量的逻辑是聚合级别的（取整个筛选上下文的最早/最晚时间），而非单条记录的工时计算，这也会影响后续求和的准确性。

修正方案 #

1. 修正Hours Worked（单条记录级） #

如果需要计算每条HOS Logs 1 0记录的工时，改为直接取当前记录的起止时间判断：

Hours Worked = 
VAR CurrentStart = 'HOS Logs 1 0'[StartTime]
VAR CurrentEnd = 'HOS Logs 1 0'[EndTime]
RETURN
IF(CurrentStart = CurrentEnd, 0, DATEDIFF(CurrentStart, CurrentEnd, HOUR))

2. 修正Total Hours Worked2（关联上下文） #

确保在迭代HOS Daily Logs 1 0时，只计算当前行对应的HOS Logs 1 0记录总和（需根据实际表关联字段调整，比如DriverID、LogDate）：

Total Hours Worked2 = 
SUMX(
    'HOS Daily Logs 1 0',
    IF(
        'HOS Daily Logs 1 0'[StartTime] = 'HOS Daily Logs 1 0'[EndTime],
        0,
        CALCULATE(
            SUMX('HOS Logs 1 0', [Hours Worked]),
            -- 替换为实际关联字段，确保上下文匹配
            'HOS Logs 1 0'[DriverID] = 'HOS Daily Logs 1 0'[DriverID],
            'HOS Logs 1 0'[LogDate] = 'HOS Daily Logs 1 0'[LogDate]
        )
    )
)

3. 简化方案（若日表已聚合） #

如果HOS Daily Logs 1 0本身是按天聚合的记录，可直接在该表上计算，无需关联原表：

Total Hours Worked2 = 
IF(
    'HOS Daily Logs 1 0'[StartTime] = 'HOS Daily Logs 1 0'[EndTime],
    0,
    DATEDIFF('HOS Daily Logs 1 0'[StartTime], 'HOS Daily Logs 1 0'[EndTime], HOUR)
)

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