Forum Discussion

Michael1's avatar
Michael1
Advocate II
9 years ago
Solved

CALCULATETABLE

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

Tutorial Requests
  • DoubleJ's avatar
    DoubleJ
    9 years ago

    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 :-)

     

     

7 Replies

  • DoubleJ's avatar
    DoubleJ
    Solution Supplier
    9 years ago

    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 :-)

     

     

    • Michael1's avatar
      Michael1
      Advocate II
      9 years ago

      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's avatar
        DoubleJ
        Solution Supplier
        9 years ago

        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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      It is Dog, I guess.. 😉