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

Selecting last survey response per respondent

Hi,

I put together a survey that has roughly the format in the attached screenshot. I would like to create a measure that will filter this table such that only the latest response per person will be considered for other measures/analysis. In my attached example, rows 2 through 5 should be filtered out. I imagine this will use some combination of LASTDATE and SUMMARIZE, but haven't gotten to this level of proficiency yet. Thanks!

 

jdriscoll_0-1642711365005.png

 

1 ACCEPTED SOLUTION
SteveHailey
Solution Specialist
Solution Specialist

Hi @jdriscoll.

 

Since you don't need the excluded data for any measures or analysis, I would remove the unneeded rows in Power Query, rather than with the DAX functions you mentioned. That will greatly simplify all your measures.

 

I attached a PBIX file with the solution here that you can download. Essentially I used the Group By function to create a table with the Max date for each Person. These are the dates we want to keep for each person. Then I used an inner-join to keep only the rows from the original table that match that new table that has the max dates.


Let me walk you through it in detail:


After entering the data into a table cleverly named "Table", I right clicked that Table in the Queries pane on the left side, and chose "Duplicate".

 

SteveHailey_0-1642715066036.png

I then right-clicked and Renamed the new query "DatesToKeep", and then selected that query. I then went to Transform > Group By and entered parameters as seen below:

SteveHailey_1-1642715173645.png

The resulting table looks like this. 

 

SteveHailey_3-1642715201810.png

I then selected the original Table in the query pane on the left, and then went to Home > Mergie Queries, and added a merge with the parameters as seen below:

SteveHailey_4-1642715287310.png

The "Inner" under Join Kind is important.

The resulting table looked like this:

SteveHailey_5-1642715341669.png

I then right clicked the DatesToKeep column and Removed it. 

The end result looks like this:

SteveHailey_6-1642715381747.png

 

You can right click the DatesToKeep query and uncheck "Enable load" so that that helper table won't be loaded to your data model. Then choose Close and Apply.

Hope that helps!

Steve

 

 

View solution in original post

2 REPLIES 2
jdriscoll
Regular Visitor

Yup, thanks a bunch! I thought Power Query might be the way to go on this one but wasn't sure of how to pull out the max date and then filter the original table on it, but this makes perfect sense.

SteveHailey
Solution Specialist
Solution Specialist

Hi @jdriscoll.

 

Since you don't need the excluded data for any measures or analysis, I would remove the unneeded rows in Power Query, rather than with the DAX functions you mentioned. That will greatly simplify all your measures.

 

I attached a PBIX file with the solution here that you can download. Essentially I used the Group By function to create a table with the Max date for each Person. These are the dates we want to keep for each person. Then I used an inner-join to keep only the rows from the original table that match that new table that has the max dates.


Let me walk you through it in detail:


After entering the data into a table cleverly named "Table", I right clicked that Table in the Queries pane on the left side, and chose "Duplicate".

 

SteveHailey_0-1642715066036.png

I then right-clicked and Renamed the new query "DatesToKeep", and then selected that query. I then went to Transform > Group By and entered parameters as seen below:

SteveHailey_1-1642715173645.png

The resulting table looks like this. 

 

SteveHailey_3-1642715201810.png

I then selected the original Table in the query pane on the left, and then went to Home > Mergie Queries, and added a merge with the parameters as seen below:

SteveHailey_4-1642715287310.png

The "Inner" under Join Kind is important.

The resulting table looked like this:

SteveHailey_5-1642715341669.png

I then right clicked the DatesToKeep column and Removed it. 

The end result looks like this:

SteveHailey_6-1642715381747.png

 

You can right click the DatesToKeep query and uncheck "Enable load" so that that helper table won't be loaded to your data model. Then choose Close and Apply.

Hope that helps!

Steve

 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors