cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davidwsw
Frequent Visitor

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:

 Competency Scores.png

 

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.

Competency Scores_LastDate.png

However, as soon as I added Score to the same visual, all the other dates appeared:

 

Competency Scores_LastScore.png

The result that I'm looking for is:

 

Competency Scores_DesiredResult.png

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.

 

1 ACCEPTED SOLUTION

@ankitpatira

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.

View solution in original post

5 REPLIES 5
ankitpatira
Community Champion
Community Champion

@davidwsw Click on the dropdown for score field and choose Maximum and you will get the output as required.

 

Capture.PNG

@ankitpatira

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.

View solution in original post

@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

 

Capture.PNGCapture2.PNGCapture3.PNGCapture4.PNG

Capture5.PNG

@ankitpatira

 

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:

Competency Scores_Solution1.png

 

 

I added another grouping by Competency:

Competency Scores_Solution2.png

 

That gave me the desired solution:

 

Competency Scores_Solution22.png

 

Thanks!

 

 

 

 

 

 

@davidwsw Yep i missed that adding extra layer of competency in grouping but glad you got it.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.