Power BI：基于时间区间度量实现环形图筛选订单表

问题背景 #

现有四个DAX度量，用于按发票时间和页面所选日期划分订单区间，将这些度量放到环形图后，无法通过点击环形图的不同区间筛选页面上的全订单表。尝试用计算列实现订单区间划分，但因度量依赖SELECTEDVALUE函数（动态上下文）而失败。

-- 原有度量
Time Frame (Same Day) = CALCULATE([Sales Credit Master],AND(SAP_Order_Export[Sales Credit Timeframe]="Same Day",SELECTEDVALUE(SAP_Order_Export[Invoice DateMaster])=SAP_Order_Export[Sales Order Date]))
 
Time Frame (Historical) = CALCULATE([Sales Credit Master], AND(SAP_Order_Export[Sales Credit Timeframe]="Historical",SELECTEDVALUE(SAP_Order_Export[InvoiceDateMaster])>SAP_Order_Export[Sales Order Date]))

Time Frame (Open) = CALCULATE([Total Open (DSR Page)],AND(SELECTEDVALUE(SAP_Order_Export[Invoice Date Master])=SAP_Order_Export[Sales Order Date],SAP_Order_Export[Invoice Date Master]=blank()))

Time Frame (Future Revenue) = CALCULATE([Total Booked],ALL(SAP_Order_Export),AND(SELECTEDVALUE('Invoice App Date'[Date])<SAP_Order_Export[Invoice Date Master],SELECTEDVALUE('Invoice App Date'[Date])=SAP_Order_Export[Sales Order Date]))

解决方案：用静态维度表+动态筛选度量实现联动 #

1. 创建时间框架维度表 #

先手动生成包含所有订单区间的维度表，作为环形图的轴字段：

Time Frame Dimension = 
DATATABLE (
    "Time Frame Name", STRING,
    {
        {"Same Day"},
        {"Historical"},
        {"Open"},
        {"Future Revenue"}
    }
)

2. 创建统一的时间框架聚合度量 #

用SWITCH函数匹配维度表的区间名称，整合原有度量的逻辑，作为环形图的数值字段：

Time Frame Filter Measure = 
VAR SelectedTimeFrame = SELECTEDVALUE('Time Frame Dimension'[Time Frame Name])
VAR SelectedDate = SELECTEDVALUE('Invoice App Date'[Date]) -- 统一使用页面日期筛选器的上下文
RETURN
SWITCH (
    SelectedTimeFrame,
    "Same Day",
        CALCULATE (
            [Sales Credit Master],
            SAP_Order_Export[Sales Credit Timeframe] = "Same Day",
            SAP_Order_Export[Invoice Date Master] = SAP_Order_Export[Sales Order Date],
            SAP_Order_Export[Invoice Date Master] = SelectedDate
        ),
    "Historical",
        CALCULATE (
            [Sales Credit Master],
            SAP_Order_Export[Sales Credit Timeframe] = "Historical",
            SAP_Order_Export[Invoice Date Master] > SAP_Order_Export[Sales Order Date],
            SAP_Order_Export[Invoice Date Master] = SelectedDate
        ),
    "Open",
        CALCULATE (
            [Total Open (DSR Page)],
            SAP_Order_Export[Invoice Date Master] = BLANK(),
            SAP_Order_Export[Sales Order Date] = SelectedDate
        ),
    "Future Revenue",
        CALCULATE (
            [Total Booked],
            SAP_Order_Export[Invoice Date Master] > SelectedDate,
            SAP_Order_Export[Sales Order Date] = SelectedDate,
            ALL(SAP_Order_Export) -- 保留原有度量的ALL上下文
        )
)

3. 创建订单表筛选度量 #

用于判断当前订单是否属于选中的时间框架，在订单表的视觉级筛选中设置该度量等于1：

Is In Selected Time Frame = 
VAR SelectedTimeFrame = SELECTEDVALUE('Time Frame Dimension'[Time Frame Name])
VAR SelectedDate = SELECTEDVALUE('Invoice App Date'[Date])
VAR CurrentInvoiceDate = MAX(SAP_Order_Export[Invoice Date Master])
VAR CurrentOrderDate = MAX(SAP_Order_Export[Sales Order Date])
VAR CurrentTimeframe = MAX(SAP_Order_Export[Sales Credit Timeframe])
RETURN
SWITCH (
    SelectedTimeFrame,
    "Same Day",
        IF (
            CurrentTimeframe = "Same Day" && 
            CurrentInvoiceDate = CurrentOrderDate && 
            CurrentInvoiceDate = SelectedDate,
            1, 0
        ),
    "Historical",
        IF (
            CurrentTimeframe = "Historical" && 
            CurrentInvoiceDate > CurrentOrderDate && 
            CurrentInvoiceDate = SelectedDate,
            1, 0
        ),
    "Open",
        IF (
            CurrentInvoiceDate = BLANK() && 
            CurrentOrderDate = SelectedDate,
            1, 0
        ),
    "Future Revenue",
        IF (
            CurrentInvoiceDate > SelectedDate && 
            CurrentOrderDate = SelectedDate,
            1, 0
        ),
    1 -- 未选中任何区间时显示全部订单
)

4. 配置视觉对象 #

• 环形图：将Time Frame Dimension[Time Frame Name]拖入轴，Time Frame Filter Measure拖入值
• 全订单表：在视觉级筛选中添加Is In Selected Time Frame，设置为等于1

原理说明 #

原有单独度量是聚合值，无法传递筛选上下文到订单表。通过创建静态的时间框架维度表，将每个区间作为可筛选的维度值，再用SWITCH动态匹配对应的筛选逻辑，就能让环形图的点击操作生成筛选上下文，进而联动订单表显示对应区间的订单。

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