cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ThaddeusB Regular Visitor
Regular Visitor

Tricky Match up of survey date in one table with survey answer in another

I have a table that lists client #s survey dates.  I also have separate tables last list answers to various questions.  I want to match them up to return the answer to a given question on the date of a survey.  The tricky part is there is not a one-to-one relationship between survey dates and answer dates.  For example, if a client was surveyed twice and the answer didn't change the answer table only has one row for that client.  So what I want to return is the newest answer on or before the survey date. Here is osme example data

 

Survey table

Survey #ClientSurvey date
112/1/2018
215/5/2018
323/4/2018
4210/5/2018
534/4/2018
641/2/2018
745/7/2018
849/9/2018



Answer table

ClientAnswerAnswer date
1Yes2/1/2018
2No1/1/2018
2Yes4/1/2018
2No9/1/2018
3No4/4/2018
3Yes5/30/2018
4No1/2/2018
4Yes9/9/2018


I would like something like a calculated column in the survey table with values like:

Survey #Q1 Answer
1Yes
2Yes
3No
4No
5No
6No
7No
8Yes

 

Any suggestions?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Tricky Match up of survey date in one table with survey answer in another

Hi @ThaddeusB

 

Try this for your new calculated column in the Survey table:

 

 

Q1Answer =
VAR _DateofInterest =
    CALCULATE (
        MAX ( Answer[Answer date] ),
        FILTER (
            Answer,
            Answer[client] = Survey[Client]
                && Answer[Answer date] <= Survey[Survey date]
        )
    )
RETURN
    LOOKUPVALUE (
        Answer[Answer],
        Answer[Client], Survey[Client],
        Answer[Answer date], _DateOfInterest
    )

 

 

 

Code formatted with   www.daxformatter.com

1 REPLY 1
Highlighted
Super User
Super User

Re: Tricky Match up of survey date in one table with survey answer in another

Hi @ThaddeusB

 

Try this for your new calculated column in the Survey table:

 

 

Q1Answer =
VAR _DateofInterest =
    CALCULATE (
        MAX ( Answer[Answer date] ),
        FILTER (
            Answer,
            Answer[client] = Survey[Client]
                && Answer[Answer date] <= Survey[Survey date]
        )
    )
RETURN
    LOOKUPVALUE (
        Answer[Answer],
        Answer[Client], Survey[Client],
        Answer[Answer date], _DateOfInterest
    )

 

 

 

Code formatted with   www.daxformatter.com