Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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 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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |