Power BI中基于周薪资数据统计月度超60工时员工数的问题

我有一张薪资数据表，结构如下：

[ Name, Date, Hours, Job Code ]
Name = 员工姓名
Date = 周度日期，间隔严格为一周
Hours = 对应Job Code、对应Date所在周的工时数
Job Code = 工作类型，分为REGULAR（常规）和OT（加班）

需要创建按月份统计周工时超过60小时员工数量的表格，满足以下条件：

• 工时统计包含所有Job Code，即常规+加班总工时
• 同一员工同一月份内有多周工时超60，仅计1次
• 仅统计工时严格大于60的情况（等于60不计入）

我写了以下DAX度量值：

// 计算某员工某日期的全Job Code总工时
HoursPerson = CALCULATE(SUM(WorkHours[Hours]), ALLSELECTED(WorkHours[Job Code]))

然后创建统计度量：

CountOver60 = COUNTAX(VALUES(WorkHours[Name]), [HoursPerson] > 60)

但结果不对，它统计了所有有记录的员工，不是符合条件的。请问哪里错了？怎么实现需求？

示例数据：

Name,Date,Hours,Job Code
Alejandro,2023/08/04,25,REGULAR
Alexander,2023/07/14,20.5,REGULAR
Arceli,2023/09/15,30,REGULAR
Chad,2023/07/14,20,REGULAR
Chad,2023/09/15,21.25,REGULAR
Christopher,2023/08/11,27.5,REGULAR
Christopher,2023/08/18,43,REGULAR
Christopher,2023/08/25,31.75,REGULAR
Daniel,2023/07/21,22,REGULAR
Daniel,2023/08/04,23.5,REGULAR
Daniel,2023/08/11,70,REGULAR
Daniel,2023/08/18,28,REGULAR
Daniel,2023/09/01,21.5,REGULAR
Darrell,2023/09/15,29,REGULAR
David,2023/07/14,21,REGULAR
David,2023/09/01,21,REGULAR
Devin,2023/09/15,65,REGULAR
Everett,2023/07/21,21,REGULAR
Everett,2023/08/25,24.5,REGULAR
Gabriel,2023/08/04,47,REGULAR
Isaias,2023/09/15,20,REGULAR
James,2023/08/11,21.5,REGULAR
Jason,2023/07/21,29.5,REGULAR
Jason,2023/08/25,38.5,REGULAR
Jason,2023/08/25,30,OT
Jeremy,2023/07/14,63,REGULAR
Jeremy,2023/07/21,21,REGULAR
Jeremy,2023/07/28,67,REGULAR
John,2023/07/14,48,REGULAR
John,2023/07/21,28,REGULAR
John,2023/07/28,65,REGULAR
John,2023/08/04,24.5,REGULAR
John,2023/08/11,22,REGULAR
John,2023/08/25,20,REGULAR
John,2023/09/22,31.5,REGULAR
Justin,2023/09/15,31.25,REGULAR
Leonides,2023/08/18,20,REGULAR
Leonides,2023/08/25,22,REGULAR
Leonides,2023/08/25,42,OT
Mario,2023/07/21,22,REGULAR
Mario,2023/07/21,24,OT
Mario,2023/07/28,40,REGULAR
Mario,2023/07/28,24,OT
Matthew,2023/07/28,24.5,REGULAR
Pedro,2023/07/14,20,REGULAR
Robert,2023/08/18,20,REGULAR
Shawn,2023/08/04,24.5,REGULAR
Shawn,2023/09/15,32.75,REGULAR
Steven,2023/07/14,22.5,REGULAR
Thomas,2023/07/21,32,REGULAR
Thomas,2023/07/28,24.5,REGULAR
Thomas,2023/09/15,31.5,REGULAR
Thomas,2023/09/22,26.5,REGULAR
Thomas,2023/07/28,20,OT
Thomas,2023/08/18,45,REGULAR
Thomas,2023/08/18,20.5,OT
Timothy,2023/07/14,20,REGULAR
Timothy,2023/08/11,25,REGULAR
Timothy,2023/08/11,45,OT
Vincent,2023/07/07,20,REGULAR
Vincent,2023/07/28,20,REGULAR
Vincent,2023/08/11,20,REGULAR
Vincent,2023/08/18,20,REGULAR
Vincent,2023/09/01,20,REGULAR
Vincent,2023/09/08,54,REGULAR
Vincent,2023/09/15,65,REGULAR
Vincent,2023/09/22,20,REGULAR

错误原因 #

1. HoursPerson 上下文错误：ALLSELECTED(WorkHours[Job Code]) 仅清除Job Code的筛选，但未锁定“员工+周”的计算层级，导致遍历员工姓名时，计算的是员工在当月的总工时，而非单周总工时。
2. COUNTAX 逻辑缺陷：COUNTAX 会对每一行的表达式结果计数，即使表达式返回FALSE（布尔值非空），也会被计入，这就是为什么统计了所有有记录的员工。

正确DAX实现 #

需要先计算员工单周总工时，再筛选出超60小时的周，最后对员工去重计数：

方法1：分步实现 #

// 计算单员工单周的总工时（常规+加班）
WeeklyTotalHours = 
CALCULATE(
    SUM(WorkHours[Hours]),
    ALLEXCEPT(WorkHours, WorkHours[Name], WorkHours[Date])
)

// 统计符合条件的员工数
CountOver60 = 
VAR Over60Weeks = 
    FILTER(
        SUMMARIZE(WorkHours, WorkHours[Name], WorkHours[Date], "Total", [WeeklyTotalHours]),
        [Total] > 60
    )
VAR DistinctEmployees = DISTINCT(SELECTCOLUMNS(Over60Weeks, "Name", WorkHours[Name]))
RETURN COUNTROWS(DistinctEmployees)

方法2：简洁版 #

CountOver60 = 
COUNTROWS(
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                SUMMARIZE(WorkHours, WorkHours[Name], WorkHours[Date], "Total", SUM(WorkHours[Hours])),
                [Total] > 60
            ),
            "Name", WorkHours[Name]
        )
    )
)

示例数据验证 #

根据示例数据，正确统计结果应为：

• 2023年7月：Jeremy、John、Mario → 3人
• 2023年8月：Daniel、Jason、Leonides、Thomas、Timothy → 5人
• 2023年9月：Devin、Vincent → 2人

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