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

     

     

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

    • Michael1's avatar
      Michael1
      Advocate II
      9 years ago

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

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