Forum Discussion
Problems with filtering table by last date
I have a table like this that I want to filter to only show the score for each user's last date and for each competency:
I created a LastDate measure:
LastDate = MAX(Table1[Date])
When I added User, Competency and LastDate to a table visual, it looks fine. I see the correct rows.
However, as soon as I added Score to the same visual, all the other dates appeared:
The result that I'm looking for is:
I then want to use that to calculate the average score for each competency across all users (e.g. for competency A that would be 44) and the average score for each user across all competencies (e.g. for Eric that would be 45).
Any help would be greatly appreciated. Thank you in advance.
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.
5 Replies
davidwsw Click on the dropdown for score field and choose Maximum and you will get the output as required.
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
