Forum Discussion
Problems with filtering table by last date
I'm not looking for the maximum score. I'm looking for each user's most recent score for each competency. For example, for John I want to get 50, not 80.
davidwsw I see what you mean. Go to power bi desktop, query editor -> right click your table and make a duplicate of it -> then under Transform tab click Group By for the duplicated query -> then group by as shown below with only two columns user and maxdate -> then under Home tab click Merge Queries -> then merge your duplicated table with two columns with original table (selecting user column first and then maxdate column ) using Inner join as shown below -> then expand result and include competency, score columns for the merged table -> close & apply
Your solution did not quite work but it pointed me in the right direction. Sorry, maybe I didn't explain myself properly. I'm looking for the score for each combination of User-Competency-MaxDate. What your solution was giving me was the score for each combination of User and MaxDate.
Your solution gave me this:
I added another grouping by Competency:
That gave me the desired solution:
Thanks!
davidwsw Yep i missed that adding extra layer of competency in grouping but glad you got it.