PowerBI中DAX计算指定日期在职员工数出错求助

问题背景 #

我在PowerBI中通过CalendarAuto()创建日期表，Staff表包含Unique ID、Current Employment: Start Date、End Date字段，需计算指定日期（如2022年10月31日）的在职员工总数。参考社区代码修改后的DAX公式仅统计当日入职人员，而非所有在职员工，新建文件测试结果一致，怀疑是日期关系或逻辑问题，寻求修复建议。

原DAX代码：

Employed Staff Count = 
VAR tmpTickets = ADDCOLUMNS('STAFFMASTER',"Effective Date",IF(ISBLANK([End Date]),TODAY(),[End Date]))
VAR tmpTable =  
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpTickets,
            'Calendar'
        ),
        [Date] >= [Current Employment: Start Date] &&
        [Date] <= [Effective Date]
    ),
    "ID",[Unique Id],
    "Date",[Date]
)
VAR tmpTable1 = GROUPBY(tmpTable,[ID],"Count",COUNTX(CURRENTGROUP(),[Date]))
RETURN COUNTROWS(tmpTable1)

问题分析 #

原公式核心问题是冗余的笛卡尔积操作：GENERATE('STAFFMASTER', 'Calendar')生成员工表与日期表的全组合，再筛选在职日期范围，这种方式效率极低，且易受报表上下文筛选（如仅选中单个日期）影响，导致逻辑偏差。此外，公式未明确绑定目标日期，依赖上下文筛选也可能引发结果不符合预期。

修复方案 #

简化逻辑，直接统计符合在职条件的员工数，无需生成笛卡尔积：

方案1：适配报表上下文（自动取选中日期，默认2022-10-31） #

Employed Staff Count = 
VAR TargetDate = SELECTEDVALUE('Calendar'[Date], DATE(2022,10,31))
RETURN
CALCULATE(
    COUNTROWS('STAFFMASTER'),
    FILTER(
        'STAFFMASTER',
        [Current Employment: Start Date] <= TargetDate &&
        (ISBLANK([End Date]) || [End Date] >= TargetDate)
    )
)

方案2：固定目标日期 #

Employed Staff Count = 
VAR TargetDate = DATE(2022,10,31) -- 直接指定需要计算的日期
RETURN
COUNTROWS(
    FILTER(
        'STAFFMASTER',
        [Current Employment: Start Date] <= TargetDate &&
        (ISBLANK([End Date]) || [End Date] >= TargetDate)
    )
)

关键逻辑说明 #

• 在职判定：入职日期≤目标日期，且（离职日期为空（仍在职） 或 离职日期≥目标日期）
• 用COUNTROWS直接统计符合条件的员工，避免笛卡尔积带来的性能与逻辑问题
• SELECTEDVALUE支持报表中手动选择日期，无选择时使用默认值

额外注意事项 #

• 确保日期表与Staff表无直接关系，或设置交叉筛选方向为“单向”（日期表→Staff表），避免上下文干扰
• 检查Staff表日期字段格式，确保为日期类型而非文本
• 若需计算动态日期（如每月末），可将TargetDate替换为EOMONTH(TODAY(), -1)等函数

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