cancel
Showing results for 
Search instead for 
Did you mean: 
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
Responsive Resident
Responsive Resident

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
Responsive Resident
Responsive Resident

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

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

DoubleJ
Responsive Resident
Responsive Resident

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?

DoubleJ
Responsive Resident
Responsive Resident

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.