How can I bring a record from another table with certain conditions from my table in DAX?

Question

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.

Table1:

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

Table2:

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

EXPECTED RESULTS:

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

Olly Olly · Accepted Answer · 2019-02-14T16:07:37

Add calculated columns to Table1:

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

and

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

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

How can I bring a record from another table with certain conditions from my table in DAX?

1 Answers