Power BI双阶段选择下基于最新阶段日期的过滤逻辑优化需求

核心逻辑 #

需求的核心规则是：

• 当用户通过「阶段」切片器选中多个阶段时，仅校验选中阶段中完成日期最晚的那个阶段是否落在「完成日期」切片器的范围内
• 若校验通过，则返回该工单所有选中的阶段数据（无论这些阶段自身的完成日期是否在切片器范围内）
• 最终目的是计算选中阶段之间的完成天数差异

SQL 实现（以SQL Server为例） #

假设你的业务数据表结构如下（请根据实际表名/字段调整）：

• 工单表：work_orders，包含字段 ticket_id（工单ID）、stage_name（阶段名称）、completion_date（阶段完成日期）

实现代码 #

-- 参数定义：@selected_stages 为选中的阶段列表（逗号分隔字符串），@start_date/@end_date 为日期切片器的起止日期
WITH ticket_selected_stages AS (
    SELECT 
        ticket_id,
        stage_name,
        completion_date,
        -- 计算当前工单选中阶段的最晚完成日期
        MAX(completion_date) OVER (PARTITION BY ticket_id) AS latest_selected_completion_date
    FROM work_orders
    -- 筛选用户选中的阶段
    WHERE stage_name IN (SELECT value FROM STRING_SPLIT(@selected_stages, ','))
)
SELECT 
    ticket_id,
    stage_name,
    completion_date,
    -- 计算选中阶段的天数差异（最早到最晚）
    DATEDIFF(day, 
             MIN(completion_date) OVER (PARTITION BY ticket_id),
             MAX(completion_date) OVER (PARTITION BY ticket_id)) AS stage_day_diff
FROM ticket_selected_stages
-- 仅保留最新阶段完成日期在指定范围内的工单
WHERE latest_selected_completion_date BETWEEN @start_date AND @end_date;

关键说明 #

1. 用CTE先筛选出所有选中阶段的工单数据，并通过窗口函数计算每个工单选中阶段的最晚完成日期
2. 外层查询仅保留最晚完成日期落在切片器范围内的工单数据，确保符合业务规则
3. 直接通过窗口函数计算选中阶段的天数差，无需额外关联

Power BI DAX 实现 #

假设你的数据模型包含：

• 事实表：WorkOrders，字段 TicketID、StageName、CompletionDate
• 维度表：StageList（用于「阶段」切片器，与WorkOrders[StageName]建立关系）、DateTable（用于「完成日期」切片器，与WorkOrders[CompletionDate]建立关系）

步骤1：创建 eligibility 筛选度量值 #

这个度量值用于判断当前工单是否符合显示条件：

IsEligibleForDisplay = 
VAR SelectedStages = VALUES(StageList[StageName])
VAR CurrentTicket = SELECTEDVALUE(WorkOrders[TicketID])
-- 获取当前工单所有选中阶段的完成日期
VAR TicketStageDates = 
    CALCULATETABLE(
        VALUES(WorkOrders[CompletionDate]),
        WorkOrders[StageName] IN SelectedStages,
        ALL(WorkOrders[StageName]) -- 忽略当前行的阶段筛选，获取所有选中阶段的日期
    )
-- 提取选中阶段的最晚完成日期
VAR LatestStageDate = MAXX(TicketStageDates, [CompletionDate])
-- 判断最晚日期是否在日期切片器的选中范围内
VAR IsLatestInRange = CONTAINS(DateTable[Date], DateTable[Date], LatestStageDate)
RETURN IF(IsLatestInRange, 1, 0)

步骤2：创建阶段天数差异度量值 #

StageDayDifference = 
VAR SelectedStages = VALUES(StageList[StageName])
VAR TicketStageDates = 
    CALCULATETABLE(
        VALUES(WorkOrders[CompletionDate]),
        WorkOrders[StageName] IN SelectedStages
    )
VAR EarliestDate = MINX(TicketStageDates, [CompletionDate])
VAR LatestDate = MAXX(TicketStageDates, [CompletionDate])
RETURN DATEDIFF(EarliestDate, LatestDate, DAY)

步骤3：应用筛选到可视化 #

在Power BI的可视化组件（如表格）中，添加筛选器：

• 选择度量值 IsEligibleForDisplay，设置为等于1

这样就只会显示符合条件的工单的所有选中阶段数据，同时可以直接使用StageDayDifference度量值展示天数差异。

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