Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jdriscoll
Helper I
Helper I

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
Helper I
Helper I

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.