cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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 III
Super User III

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

View solution in original post

1 REPLY 1
Highlighted
Super User III
Super User III

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

View solution in original post

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors