如何在DAX中以特定条件从另一个表中获取记录？ [英] How can I bring a record from another table with certain conditions from my table in DAX?

问题描述

我有两个桌子。一个带有日期和客户ID的技术支持[表1]，另一个带有向客户发送的调查问卷[表2]）。问题是调查在服务完成后的几天内发送。因此，我需要从调查表中找到日期最接近的调查ID，并将其带到我的技术支持表中。这是我的数据示例和想要的结果。

I have two tables. One with dates and customer ID's of tech support given [Table 1], and the other one with surveys sent to the customers [Table 2]). The problem is that the surveys are sent some days after the service is done. So, I need to find the survey ID with the closest date from the survey table and bring it to my tech support table. Here's a sample of my data and the result wanted.

表1：

TechSupportDate   CustomerID
01/12/2018          1
02/12/2018          2
05/12/2018          1

表2：

SurveyID SurveyDate   CustomerID   
1001     04/12/2018     1
1002     04/12/2018     2
1003     10/12/2018     1

期望的结果：

TechSupportDate    CustomerID SurveyDate     SurveyID
01/12/2018          1         04/12/2018      1001
02/12/2018          2         04/12/2018      1002
05/12/2018          1         10/12/2018      1003

推荐答案

将计算列添加到表1：

SurveyDate = 
CALCULATE ( 
    MIN ( Table2[SurveyDate] ),
    FILTER ( 
        Table2,
        Table2[SurveyDate] >= Table1[TechSupportDate] && Table2[CustomerID] = Table1[CustomerID]
    )
)

和

SurveyID = 
CALCULATE ( 
    FIRSTNONBLANK ( Table2[SurveyID], 1 ),
    FILTER ( 
        Table2,
        Table2[SurveyDate] = Table1[SurveyDate] && Table2[CustomerID] = Table1[CustomerID]
    )
)

这是一个可行的PBIX示例： https://excel.solutions/so_54693431/

Here's a worked example PBIX: https://excel.solutions/so_54693431/

这篇关于如何在DAX中以特定条件从另一个表中获取记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！