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.
Hello BI Community:
I am trying to calculate a table that will filter distinct records based on a single value.
My data looks like this:
Student ID Number FName LName AssessmentNumber Score
12345 Mickey Mouse 3 87
12345 Mickey Mouse 2 45
12345 Mickey Mouse 1 40
23456 Donald Duck 2 70
23456 Donald Duck 1 75
34567 Goofy Dog 1 25
I would like to then have a table that looks like this:
Student ID Number FName LName AssessmentNumber Score
12345 Mickey Mouse 3 87
23456 Donald Duck 2 70
34567 Goofy Dog 1 25
As you see, students took the same assessment multiple times and I want to display aggregate data based on the most recent assessment (while leaving the historical data intact).
Any suggestions will be greatly appreciated!
Thank you,
Michael
Solved! Go to Solution.
Hi @Michael1
You could create a column that checks wheter the current row of the student is his last assessment (you might have to replace semicolons with commas):
IsLastAssessment = IF(Assessments[AssNr] = MAXX( FILTER( Assessments; Assessments[StudentID]= EARLIER(Assessments[StudentID]) ); Assessments[AssNr]); TRUE(); FALSE() )
Then you can use this column to filter you visual
I hope this helps!
JJ
BTW, I didn't know Goofy Last Name 🙂
Hi @Michael1
You could create a column that checks wheter the current row of the student is his last assessment (you might have to replace semicolons with commas):
IsLastAssessment = IF(Assessments[AssNr] = MAXX( FILTER( Assessments; Assessments[StudentID]= EARLIER(Assessments[StudentID]) ); Assessments[AssNr]); TRUE(); FALSE() )
Then you can use this column to filter you visual
I hope this helps!
JJ
BTW, I didn't know Goofy Last Name 🙂
It is Dog, I guess.. 😉
Perfect! Thank you so much.
Is there a way to do this by date as well? So--if I have the assessment date and need it to select the most recent?
I really appreciate your help!
Michael
Sure!
Just compare on the date instead of the Nr
IsLastAssessmentDate = IF(Assessments[AssDate] = MAXX( FILTER( Assessments; Assessments[StudentID]= EARLIER(Assessments[StudentID]) ); Assessments[AssDate]); TRUE(); FALSE() )
Cheers!
JJ
Thanks! I actually just figured that one out. May I ask you one more question--does the data first need to be sorted, or could the rows be random?
data can be completely random, actually there's no way of sorting data in DAX (afaik)..
Thanks! I guess I didn't know if it had to be sorted in the query editor first, so that it would pick the first row. You've taught me some new DAX.
I really appreciate 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 |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |