基于DAX在Power BI表格中按日期规则筛选目标KPI

需求说明 #

需要在Monthly KPI Log表中识别满足以下规则的KPI Name，并生成Yes/No列用于筛选：

1. 该KPI在其可用日期范围内，存在连续3个月的值为M或H（任意组合：MMH、MHH、MMM、HHH、HMM、HHM），且该连续时段不晚于该KPI的最后可用日期；
2. 在该连续3个月之后，不能出现非M/H/空的值（例如示例中的"Average Resolution Time"不符合此规则，因为其连续3个月后出现了L）。

涉及字段：

• Monthly KPI Log'[KPI Name]
• Monthly KPI Log'[Value]
• Monthly KPI Log'[Metric Collection Date]

示例数据 #

解决方案：DAX计算列 #

通过创建DAX计算列实现需求，核心逻辑分为两步：先判断是否存在符合条件的连续3个月时段，再验证该时段之后无违规值。

符合规则 = 
VAR CurrentKPI = 'Monthly KPI Log'[KPI Name]
// 获取当前KPI的所有记录并按日期排序
VAR KPIData = 
    CALCULATETABLE(
        ADDCOLUMNS(
            'Monthly KPI Log',
            "日期排序", RANKX(FILTER('Monthly KPI Log', 'Monthly KPI Log'[KPI Name] = CurrentKPI), 'Monthly KPI Log'[Metric Collection Date],, ASC, DENSE)
        ),
        'Monthly KPI Log'[KPI Name] = CurrentKPI
    )
// 获取当前KPI最后可用日期的排序值
VAR LastDateRank = MAXX(KPIData, [日期排序])
// 筛选出所有连续3个月为M/H的起始记录
VAR ValidSequences = 
    FILTER(
        KPIData,
        [日期排序] <= LastDateRank - 2 &&
        'Monthly KPI Log'[Value] IN {"M", "H"} &&
        LOOKUPVALUE('Monthly KPI Log'[Value], 'Monthly KPI Log'[KPI Name], CurrentKPI, 'Monthly KPI Log'[Metric Collection Date], DATEADD('Monthly KPI Log'[Metric Collection Date], 1, MONTH)) IN {"M", "H"} &&
        LOOKUPVALUE('Monthly KPI Log'[Value], 'Monthly KPI Log'[KPI Name], CurrentKPI, 'Monthly KPI Log'[Metric Collection Date], DATEADD('Monthly KPI Log'[Metric Collection Date], 2, MONTH)) IN {"M", "H"}
    )
// 判断是否存在有效连续时段
VAR HasValidSequence = NOT ISEMPTY(ValidSequences)
// 验证连续时段后无违规值
VAR CheckPostSequence = 
    IF(
        HasValidSequence,
        VAR MinValidStartRank = MINX(ValidSequences, [日期排序])
        VAR PostSequenceData = FILTER(KPIData, [日期排序] > MinValidStartRank + 2)
        VAR HasInvalidValue = COUNTROWS(FILTER(PostSequenceData, NOT('Monthly KPI Log'[Value] IN {"M", "H", BLANK()}))) > 0
        RETURN NOT HasInvalidValue,
        FALSE
    )
RETURN
IF(HasValidSequence && CheckPostSequence, "Yes", "No")

逻辑解释 #

1. 数据排序：为每个KPI的记录按日期生成排序值，便于后续判断连续时段；
2. 识别有效连续时段：筛选出所有可作为连续3个月起始的记录，确保这三个月的值均为M或H，且起始位置不超过最后可用日期前2个月；
3. 验证后续值合规性：若存在有效连续时段，取最早的起始时段，检查该时段之后的所有记录，确认无违规值（非M/H/空）；
4. 输出结果：同时满足两个规则返回"Yes"，否则返回"No"。

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