基于筛选器选定财年动态获取对应生效Type维度值的DAX实现咨询

可实现性结论 #

该需求完全可以通过DAX实现，常规数据量级下性能表现达标，不需要优先走SQL预生成宽表的方案。

前置配置 #

首先新建一张独立的财年映射计算表，不需要和其他表建立关联，该表的「财年」字段直接用作页面筛选器：

FY 映射表 = 
DATATABLE (
    "财年", STRING,
    "财年截止日", DATETIME,
    {
        { "FY20", DATE ( 2020, 5, 31 ) },
        { "FY21", DATE ( 2021, 5, 31 ) },
        { "FY22", DATE ( 2022, 5, 31 ) }
    }
)

核心DAX度量值 #

编写如下度量值，放到表格视觉对象中即可输出对应财年的生效Type：

生效Type = 
// 读取筛选器选中财年对应的截止日
VAR Selected_FY_End = SELECTEDVALUE ( 'FY 映射表'[财年截止日] )
// 读取当前上下文的客户ID
VAR Current_Customer = SELECTEDVALUE ( Revenue[客户ID] )
// 查找该客户截止日前最大的生效日期
VAR Latest_Effect_Date =
    CALCULATE (
        MAX ( 'Type'[生效日] ),
        'Type'[客户ID] = Current_Customer,
        'Type'[生效日] <= Selected_FY_End,
        REMOVEFILTERS ( 'Type' )
    )
// 返回对应生效日期的Type值
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Type'[Type值] ),
        'Type'[客户ID] = Current_Customer,
        'Type'[生效日] = Latest_Effect_Date,
        REMOVEFILTERS ( 'Type' )
    )

性能说明 #

• 导入模式下Power BI会自动为所有字段生成列存储索引，只要Type表数据量在10万行级以内，查询响应速度可以稳定在100ms以内，无明显卡顿
• 若数据量超过千万行、Type变更记录超过百万行，可选择两种优化方案：
  • 方案1：新增计算列提前为每个客户的Type记录标注生效时间区间，度量值直接做区间匹配即可
  • 方案2：用SQL预生成每个客户对应每个财年的生效Type宽表，直接导入模型使用，性能最优

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