Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 # | Client | Survey date |
1 | 1 | 2/1/2018 |
2 | 1 | 5/5/2018 |
3 | 2 | 3/4/2018 |
4 | 2 | 10/5/2018 |
5 | 3 | 4/4/2018 |
6 | 4 | 1/2/2018 |
7 | 4 | 5/7/2018 |
8 | 4 | 9/9/2018 |
Answer table
Client | Answer | Answer date |
1 | Yes | 2/1/2018 |
2 | No | 1/1/2018 |
2 | Yes | 4/1/2018 |
2 | No | 9/1/2018 |
3 | No | 4/4/2018 |
3 | Yes | 5/30/2018 |
4 | No | 1/2/2018 |
4 | Yes | 9/9/2018 |
I would like something like a calculated column in the survey table with values like:
Survey # | Q1 Answer |
1 | Yes |
2 | Yes |
3 | No |
4 | No |
5 | No |
6 | No |
7 | No |
8 | Yes |
Any suggestions?
Solved! Go to Solution.
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 )
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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |