PowerBI中用DAX基于参考表新增关联Day ref列的需求

需求说明 #

现有三张表：ProductDate、ProductStock、DayReference，需在ProductDate表中新增「Relevant Day ref」列，根据每行的产品编号和星期几匹配ProductStock表对应的Day ref值，且不建立多对多关系。已知ProductStock表中同一产品的同一星期几不会对应多个Day ref。

各表结构 #

ProductDate表

ProductStock表

DayReference表

预期结果 #

实现方案 #

方案一：直接计算列匹配 #

在ProductDate表中添加计算列，使用以下DAX公式：

Relevant Day ref = 
VAR CurrentProduct = ProductDate[Product number]
VAR CurrentDay = ProductDate[Day of week]
RETURN
CALCULATE(
    VALUES(ProductStock[Day ref]),
    FILTER(
        ALL(ProductStock),
        ProductStock[Product number] = CurrentProduct
        && ProductStock[Day ref] IN CALCULATETABLE(
            VALUES(DayReference[Day ref]),
            DayReference[Day of week] = CurrentDay
        )
    )
)

公式解释

1. 定义变量：获取当前行的产品编号和星期几，存入CurrentProduct和CurrentDay
2. 筛选匹配：从DayReference表中找出当前星期几对应的所有Day ref，再筛选ProductStock表中产品编号匹配且Day ref属于该集合的记录
3. 返回结果：用VALUES提取唯一的Day ref值（已知无重复，结果唯一）

方案二：辅助表优化（适合大数据量） #

先创建辅助表提前生成映射关系，再用LOOKUPVALUE匹配，性能更优：

步骤1：创建辅助表

ProductDayMapping = 
SELECTCOLUMNS(
    NATURALINNERJOIN(ProductStock, DayReference),
    "Product number", ProductStock[Product number],
    "Day of week", DayReference[Day of week],
    "Day ref", ProductStock[Day ref]
)

步骤2：在ProductDate表添加计算列

Relevant Day ref = 
LOOKUPVALUE(
    ProductDayMapping[Day ref],
    ProductDayMapping[Product number], ProductDate[Product number],
    ProductDayMapping[Day of week], ProductDate[Day of week]
)

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