PowerBI+DAX问题：时段内在职公民计数错误排查求助

需求背景 #

• 制作折线图展示指定时间点的在职公民唯一计数
• 核心规则：
  • 雇佣周期覆盖某月份的公民，需计入该月在职人员（例：2024-07-20至2024-09-05的雇佣，需计入2024年7、8、9月）
  • 同一公民存在重叠雇佣记录时，单月仅计数一次

数据模型结构 #

• 事实表Ansaettelser：存储字段包括雇佣开始日期Startdato、雇佣结束日期Slutdato、公民唯一标识Borger
• 日期表Dimension_Dato：与Ansaettelser通过Dimension_Dato[Dato] → Ansaettelser[Slutdato]建立一对多关系

原DAX度量代码 #

aktive_ansaettelser_keepfilter = 
VAR MinDate = MIN(Dimension_Dato[Dato])
VAR MaxDate = MAX(Dimension_Dato[Dato])
VAR Result =
    CALCULATE(
        SUMX(DISTINCT(Ansaettelser[Borger]), 1),
        KEEPFILTERS(Ansaettelser[Startdato] <= MaxDate), 
        KEEPFILTERS(
            IF(
                OR(ISBLANK(Ansaettelser[Slutdato]), Ansaettelser[Slutdato] > TODAY()),
                TODAY() + 365,
                Ansaettelser[Slutdato]
            ) >= MinDate
        ),
        REMOVEFILTERS(Ansaettelser[Slutdato])
    )
RETURN
    Result

问题根源分析 #

1. 关系过滤冲突：模型中默认的Dimension_Dato与Ansaettelser的关系会自动筛选Slutdato匹配当前日期的记录，尽管使用了REMOVEFILTERS(Ansaettelser[Slutdato])，但KEEPFILTERS的优先级和逻辑冲突，导致部分跨月雇佣记录被错误排除。
2. 日期范围判断模糊：原度量用筛选日期的最小/最大值（MinDate/MaxDate）判断雇佣覆盖，未明确锁定月度的起始和结束边界，对于跨月雇佣的判断逻辑存在漏洞。
3. 在职员工结束日期处理不当：用TODAY() + 365替代未结束的雇佣日期，可能在未来日期筛选时出现逻辑错误，且极大值的选择不够严谨。

修正后的DAX度量 #

aktive_ansaettelser_corrected = 
VAR CurrentMonthStart = EOMONTH(MIN(Dimension_Dato[Dato]), -1) + 1
VAR CurrentMonthEnd = EOMONTH(MAX(Dimension_Dato[Dato]), 0)
VAR ActiveEmployees = 
    CALCULATETABLE(
        DISTINCT(Ansaettelser[Borger]),
        // 校验雇佣周期与当前月份是否存在重叠
        Ansaettelser[Startdato] <= CurrentMonthEnd,
        COALESCE(Ansaettelser[Slutdato], DATE(9999, 12, 31)) >= CurrentMonthStart,
        // 彻底移除日期表的默认过滤影响
        REMOVEFILTERS(Dimension_Dato)
    )
RETURN
    COUNTROWS(ActiveEmployees)

修正要点说明 #

• 精准锁定月度边界：通过EOMONTH函数计算当前筛选月份的第一天和最后一天，确保雇佣周期的重叠判断完全贴合月度范围。
• 简化重叠逻辑：直接判断雇佣开始日期≤当月末、结束日期（为空则用DATE(9999,12,31)作为永久在职标记）≥当月初，准确识别所有符合条件的在职公民。
• 解除关系干扰：使用REMOVEFILTERS(Dimension_Dato)彻底消除默认日期关系带来的筛选限制，避免记录被错误过滤。
• 优化计数逻辑：用COUNTROWS(DISTINCT(...))替代SUMX的方式，逻辑更直观，计算效率更高。

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