Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Michael1
Helper II
Helper II

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

1 ACCEPTED SOLUTION
DoubleJ
Solution Supplier
Solution Supplier

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

CalcTable_01.PNG

 

Then you can use this column to filter you visual

 

CalcTable_02.PNG

 

I hope this helps!

JJ

 

BTW, I didn't know Goofy Last Name 🙂

 

 

View solution in original post

7 REPLIES 7
DoubleJ
Solution Supplier
Solution Supplier

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

CalcTable_01.PNG

 

Then you can use this column to filter you visual

 

CalcTable_02.PNG

 

I hope this helps!

JJ

 

BTW, I didn't know Goofy Last Name 🙂

 

 

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.