如何在Power BI中使用DAX度量值计算总工时时长？

现有一张名为schedule的表格，数据如下：

需求：创建DAX度量值，计算每一组有效Start Shift与End Shift的时长，格式为“Xhr Xm”。连续多个Start Shift或End Shift视为数据错误，仅计算有效配对（最近的未配对Start对应下一个End）。已知有效配对及时长：

• 行2-行1 = 6hr 13m
• 行7-行4 = 18hr 12m
• 行12-行9 = 6hr 12m
• 总时长 = 30hr 37m

无数据转换权限，仅能通过DAX度量值实现。

实现思路 #

1. 构建完整时间戳：合并日期和时间字段为datetime格式，统一时间计算基准。
2. 过滤核心事件：仅保留Start Shift和End Shift记录，排除无关状态。
3. 配对逻辑实现：通过累计计数标记Shift状态（Start+1，End-1），确保每个End仅匹配最近的未闭合Start，自动跳过连续重复的无效事件。
4. 时长计算与格式化：对有效配对计算时间差，转换为“Xhr Xm”格式；汇总所有有效时长得到总时长。

DAX度量值代码 #

1. 单组有效配对时长（用于表格明细展示） #

有效Shift时长 = 
VAR CurrentRow = SELECTEDVALUE(schedule[行号])
VAR CurrentStatus = SELECTEDVALUE(schedule[状态])
-- 过滤出核心Shift事件
VAR AllShiftEvents = 
    FILTER(ALL(schedule), schedule[状态] IN {"Start Shift", "End Shift"})
-- 生成完整时间戳并保留行号
VAR SortedEvents = 
    ADDCOLUMNS(
        AllShiftEvents,
        "@FullTime", DATEVALUE(schedule[日期]) + TIMEVALUE(schedule[时间]),
        "@RowNumber", schedule[行号]
    )
-- 按时间排序生成排名
VAR RankedEvents = 
    ADDCOLUMNS(
        SortedEvents,
        "@Rank", RANKX(ALL(SortedEvents), [@FullTime],, ASC, Dense)
    )
-- 计算累计配对计数，跟踪Shift开闭状态
VAR PairedEvents = 
    ADDCOLUMNS(
        RankedEvents,
        "@PairCount", 
            CALCULATE(
                SUMX(
                    FILTER(RankedEvents, [@Rank] <= EARLIER([@Rank])),
                    IF([状态] = "Start Shift", 1, -1)
                )
            )
    )
-- 获取当前行的配对计数
VAR CurrentPairCount = 
    MAXX(FILTER(PairedEvents, [@RowNumber] = CurrentRow), [@PairCount])
-- 找到当前End对应的最近未配对Start时间
VAR StartTime = 
    IF(
        CurrentStatus = "End Shift" && CurrentPairCount = 0,
        MAXX(
            FILTER(
                PairedEvents,
                [@Rank] < MAXX(FILTER(PairedEvents, [@RowNumber] = CurrentRow), [@Rank])
                && [状态] = "Start Shift"
                && CALCULATE(
                    SUMX(FILTER(PairedEvents, [@Rank] <= EARLIER([@Rank])), IF([状态] = "Start Shift", 1, -1)) = 1
                )
            ),
            [@FullTime]
        )
    )
-- 获取当前End的时间
VAR EndTime = 
    IF(
        CurrentStatus = "End Shift" && CurrentPairCount = 0,
        MAXX(FILTER(PairedEvents, [@RowNumber] = CurrentRow), [@FullTime])
    )
-- 计算时长并格式化
VAR DurationMinutes = DATEDIFF(StartTime, EndTime, MINUTE)
VAR Hours = INT(DurationMinutes / 60)
VAR Minutes = MOD(DurationMinutes, 60)
RETURN
    IF(CurrentStatus = "End Shift" && CurrentPairCount = 0, Hours & "hr " & Minutes & "m", BLANK())

2. 总时长度量值（用于卡片视觉对象） #

总Shift时长 = 
-- 过滤核心Shift事件
VAR AllShiftEvents = 
    FILTER(ALL(schedule), schedule[状态] IN {"Start Shift", "End Shift"})
-- 生成完整时间戳并保留行号
VAR SortedEvents = 
    ADDCOLUMNS(
        AllShiftEvents,
        "@FullTime", DATEVALUE(schedule[日期]) + TIMEVALUE(schedule[时间]),
        "@RowNumber", schedule[行号]
    )
-- 按时间排序生成排名
VAR RankedEvents = 
    ADDCOLUMNS(
        SortedEvents,
        "@Rank", RANKX(ALL(SortedEvents), [@FullTime],, ASC, Dense)
    )
-- 计算累计配对计数，跟踪Shift开闭状态
VAR PairedEvents = 
    ADDCOLUMNS(
        RankedEvents,
        "@PairCount", 
            CALCULATE(
                SUMX(
                    FILTER(RankedEvents, [@Rank] <= EARLIER([@Rank])),
                    IF([状态] = "Start Shift", 1, -1)
                )
            )
    )
-- 筛选出有效End事件（配对计数为0，代表闭合了一个Start）
VAR ValidEndEvents = 
    FILTER(PairedEvents, [状态] = "End Shift" && [@PairCount] = 0)
-- 为每个有效End匹配对应的Start时间
VAR StartEndPairs = 
    ADDCOLUMNS(
        ValidEndEvents,
        "@StartTime",
            MAXX(
                FILTER(
                    PairedEvents,
                    [@Rank] < EARLIER([@Rank])
                    && [状态] = "Start Shift"
                    && CALCULATE(
                        SUMX(FILTER(PairedEvents, [@Rank] <= EARLIER([@Rank])), IF([状态] = "Start Shift", 1, -1)) = 1
                    )
                ),
                [@FullTime]
            )
    )
-- 计算总分钟数并转换为小时+分钟格式
VAR TotalMinutes = SUMX(StartEndPairs, DATEDIFF([@StartTime], [@FullTime], MINUTE))
VAR TotalHours = INT(TotalMinutes / 60)
VAR TotalRemainingMinutes = MOD(TotalMinutes, 60)
RETURN
    TotalHours & "hr " & TotalRemainingMinutes & "m"

说明 #

• 有效Shift时长用于表格视觉对象，仅在有效End Shift行返回对应配对时长，其他行显示空白。
• 总Shift时长用于卡片视觉对象，直接返回所有有效配对的累计时长，可得到需求中的30hr 37m结果。
• 核心逻辑通过@PairCount字段跟踪Shift的开闭状态，自动忽略连续重复的Start或End事件，确保仅计算有效配对。

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