Power BI DAX中基于最新日期的条件LOOKUPVALUE查询需求

Excel 解法 #

1. 预处理表2：选中表2所有数据，按「Job ID」升序、「日期」降序排序，确保每个Job ID的最新记录排在该分组最顶部。
2. 在表1的目标Business列（假设原Business在B列，新列在C列）输入公式：
   =IFERROR(VLOOKUP(A2, 表2!A:C, 3, FALSE), B2)
   • 逻辑：用表1的Job ID（A2）在表2中精确匹配，找到对应记录就返回表2的Business值；如果无匹配（比如Job ID 124），则保留表1原有的Business值。

SQL 解法 #

方法1：子查询定位最大日期 #

SELECT 
    t1.JobID,
    COALESCE(t2_latest.Business, t1.Business) AS Business
FROM 表1 t1
LEFT JOIN (
    SELECT 
        t2.JobID,
        t2.Business
    FROM 表2 t2
    INNER JOIN (
        SELECT JobID, MAX(日期) AS latest_date
        FROM 表2
        GROUP BY JobID
    ) t2_max ON t2.JobID = t2_max.JobID AND t2.日期 = t2_max.latest_date
) t2_latest ON t1.JobID = t2_latest.JobID;

方法2：窗口函数标记最新记录 #

SELECT 
    t1.JobID,
    COALESCE(t2_ranked.Business, t1.Business) AS Business
FROM 表1 t1
LEFT JOIN (
    SELECT 
        JobID,
        Business,
        ROW_NUMBER() OVER (PARTITION BY JobID ORDER BY 日期 DESC) AS rn
    FROM 表2
) t2_ranked ON t1.JobID = t2_ranked.JobID AND t2_ranked.rn = 1;

• 逻辑：用ROW_NUMBER()按Job ID分组、日期降序编号，rn=1即为每个Job ID的最新记录；左连接表1后，通过COALESCE优先取表2的最新Business值，无匹配时保留表1原值。

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