如何用Power Query或DAX实现带日期维度的跨表动态公式计算

一、Power Query 方案（适配动态文本公式） #

适用于 Table_2 中 Formula 为任意文本格式运算式的场景，步骤如下：

1. 数据预处理

   • 确保两张表的 Date 列统一为日期类型，Simple（Table_1）与 Complex（Table_2）为文本类型，避免匹配错误。
   • 加载 Table_1 到 Power Query 编辑器，筛选出 ISBLANK(ActualValue) 的行（即 Simple 为 A、G 的记录）。

2. 合并表与自定义计算函数

   • 将筛选后的 Table_1 与 Table_2 按 Simple = Complex 合并，保留 Date、Formula 列。
   • 创建自定义函数，用于替换公式中的字段并计算结果：

     GetCalculatedResult = (formula as text, currentDate as date) as number =>
     let
         // 获取当前日期下所有非空的 ActualValue 记录，转为字段-值的映射
         ValueMap = Record.FromList(
             Table.SelectRows(Table_1, each [Date] = currentDate and not ISBLANK([ActualValue]))[ActualValue],
             Table.SelectRows(Table_1, each [Date] = currentDate and not ISBLANK([ActualValue]))[Simple]
         ),
         // 替换公式中的字段名为对应值
         ReplacedFormula = List.Accumulate(
             Record.FieldNames(ValueMap),
             formula,
             (state, current) => Text.Replace(state, current, Text.From(Record.Field(ValueMap, current)))
         ),
         // 执行计算并处理错误
         CalculatedValue = try Expression.Evaluate(ReplacedFormula, ValueMap) otherwise null
     in
         CalculatedValue
     

3. 填充 Result 列并合并回原表

   • 添加自定义列，调用上述函数计算结果：Result = GetCalculatedResult([Formula], [Date])
   • 将计算后的行与原 Table_1 中 ActualValue 非空的行合并，最终加载回数据模型。

二、DAX 方案（适配预定义或可映射的公式） #

DAX 对动态字符串公式的支持有限，适合 Formula 为固定运算逻辑或可拆解为DAX表达式的场景：

1. 建立关系

   • 在数据模型中创建关系：Table_1[Simple] = Table_2[Complex]，同时将 Date 作为筛选上下文的关键维度。

2. 编写计算列
   以 Formula 为类似 B + C*0.8 的场景为例，编写计算列：

   Result = 
   IF(
       ISBLANK(Table_1[ActualValue]),
       VAR CurrentDate = Table_1[Date]
       VAR ValueB = CALCULATE(MAX(Table_1[ActualValue]), Table_1[Simple] = "B", Table_1[Date] = CurrentDate)
       VAR ValueC = CALCULATE(MAX(Table_1[ActualValue]), Table_1[Simple] = "C", Table_1[Date] = CurrentDate)
       VAR FormulaLogic = LOOKUPVALUE(Table_2[Formula], Table_2[Complex], Table_1[Simple])
       // 根据 Formula 匹配对应的计算逻辑（若Formula固定，可直接写运算式）
       RETURN
           SWITCH(
               FormulaLogic,
               "B + C*0.8", ValueB + ValueC*0.8,
               "B - C/2", ValueB - ValueC/2,
               // 其他公式分支
               BLANK()
           ),
       Table_1[ActualValue]
   )
   

   若需支持更灵活的公式，可借助 DAX 2022+ 支持的自定义函数，但需确保公式符合DAX语法。

注意事项 #

• 确保 Date 列的粒度完全一致（无时分秒差异），避免匹配偏差。
• Power Query 方案需处理字段缺失的错误情况（如公式中的字段在当前日期无对应值），可在自定义函数中添加 try...otherwise 逻辑。
• 若 Formula 为完全动态的文本表达式，优先选择 Power Query 方案；若为固定业务逻辑，DAX 方案性能更优。

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