Input the most recent teacher with DAX

[botaac]

Hi Everyone,

I have the following data.

Student Name	Teacher	Grade
Bob Smith	Phil Seger	8th grade
Bob Smith	Craig Steven	9th grade
Bob Smith	Teresa White	10th grade
Jim Doe	Larry Daniels	8th grade
Jim Doe	Roger Hall	9th grade
Jim Doe	Linda Lou	10th grade

I want to create an additional column with DAX that would pull the most recent teacher. Therefore, the table would look like:

Student Name	Teacher	Grade	Most Recent Teacher
Bob Smith	Phil Seger	8th grade	Teresa White
Bob Smith	Craig Steven	9th grade	Teresa White
Bob Smith	Teresa White	10th grade	Teresa White
Jim Doe	Larry Daniels	8th grade	Linda Lou
Jim Doe	Roger Hall	9th grade	Linda Lou
Jim Doe	Linda Lou	10th grade	Linda Lou

How would I go about setting this up in DAX?

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[smpa01]

@botaac  in case you needed a measure

_mostRecentTeacher = 
CALCULATE (
    CALCULATE (
        MAX ( t2[Teacher] ),
        FILTER (
            t2,
            CONVERT ( SUBSTITUTE ( t2[Grade], "th grade", "" ), INTEGER )
                = MAXX (
                    FILTER ( t2, EARLIER ( t2[Student Name] ) = t2[Student Name] ),
                    CONVERT ( SUBSTITUTE ( t2[Grade], "th grade", "" ), INTEGER )
                )
        )
    ),
    ALLEXCEPT ( t2, t2[Student Name] )
)

[Jihwan_Kim · Accepted solution]

Hi, 

Please check the below picture and the attached pbix file.

Most Recent Teacher CC =

VAR currentstudent = Data[Student Name]

VAR addgradenumber =

ADDCOLUMNS (

FILTER ( Data, Data[Student Name] = currentstudent ),

"@gradenumber", INT ( LEFT ( Data[Grade], SEARCH ( "th", Data[Grade] ) - 1 ) )

)

VAR lastgrade =

MAXX ( addgradenumber, [@gradenumber] )

VAR newtable_teachername =

SUMMARIZE (

FILTER ( addgradenumber, [@gradenumber] = lastgrade ),

Data[Teacher]

)

RETURN

newtable_teachername

[botaac]

Thank you for your assistance! This worked great, best wishes and be safe out there.