CALCULATETABLE

[Michael1]

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

[DoubleJ · Accepted 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 🙂

[Anonymous]

It is Dog, I guess.. 😉

[Michael1]

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

[DoubleJ]

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

[Michael1]

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?

[DoubleJ]

data can be completely random, actually there's no way of sorting data in DAX (afaik)..

[Michael1]

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.