Custom Column Based on Groups in 2 Other Columns

Here is a pbix file that contains one possible solution.
It uses Table.Group and Table.TransformColumns to work on nested tables in order to get the desired result.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZIxC8IwEIX/SshcaBJtETebSXAQdCsdgg01YNOSxP9vHBSqcHe6Jcfdx713r235jhf8YE1vQ7y6OX/qUq1LJVTFpNwKkSunZEKyfX6dg3He+YHpaZxvNlneFSTE3rNjmIZgY/wTI5+Ydz/EyIXx7t3FJDf5L0y1EJT3eoF+mF+q+WQ0KEMRTEUpZF9RktpQrG3A86woQSEgqIIATE1Ro+GwwSkhDMMR0ehJ8AXweWCH7gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, Training = _t, #"TrainingStatus DateTime" = _t, #"Training Status" = _t, #"Desired Result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"Training", type text}, {"TrainingStatus DateTime", type datetime}, {"Training Status", type text}, {"Desired Result", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee Name", "Training"}, {{"AllRows", each _, type table [Employee Name=nullable text, Training=nullable text, TrainingStatus DateTime=nullable datetime, Training Status=nullable text, Desired Result=nullable text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.AddColumn(_, "Result",  (r)=> if r[#"TrainingStatus DateTime"] = List.Max([#"TrainingStatus DateTime"]) then r[#"Training Status"] else null, type text), type table}}),
    Custom2 = Table.TransformColumns(Custom1, {{"AllRows", each Table.FillUp(Table.ReplaceValue(_, each [Result], each if [Result] = null then null else if [Result] = "Complete" then "Training Complete" else "In Training", Replacer.ReplaceValue, {"Result"}), {"Result"}), type table}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(Custom2, "AllRows", {"TrainingStatus DateTime", "Training Status", "Desired Result", "Result"}, {"TrainingStatus DateTime", "Training Status", "Desired Result", "Result"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded AllRows",{{"TrainingStatus DateTime", type datetime}, {"Training Status", type text}, {"Desired Result", type text}, {"Result", type text}})
in
    #"Changed Type1"

Here's a somewhat different method that seems to execute more rapidly. All of the transformations are done within the Table.Group aggregation:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfJJTUxJLSrOyCwAcsz0jUz0jQyMTBUMDa0MDIAiwSWJRSWpKUqxOkQp98xTCCjKTy9KLS4mQoshSItzfm5BTmpJKlw9UCC3NC8zObEkMz8PQ4spFkcR1oHuLieCuozQ7CGoA6v/CeoyskAPAie8QWaMHilEKMfmKDxazNBd5Iw/EtFjhAjl6C5yJhhM2CwhrAPFnlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, Training = _t, #"TrainingStatus DateTime" = _t, #"Training Status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
                        {"Employee Name", type text}, {"Training", type text}, 
                        {"TrainingStatus DateTime", type datetime}, 
                        {"Training Status", type text}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee Name", "Training"}, {
        {"Desired Result", (t)=>
            [a=Table.Sort(t, each [TrainingStatus DateTime]),
             b=Table.DuplicateColumn(a,"Training Status","Desired Result"),
             c=Table.ReplaceValue(
                 b,
                 each [Training Status],
                 List.Last(b[Training Status]),
                 (x,y,z) as text => if z="Complete" then "Training Complete"
                                    else if z="In Progress" then "In Training"
                                    else z,
                {"Desired Result"})][c],
                type table[TrainingStatus DateTime=date, Training Status=text, Desired Result=text]
        }}),

    #"Expanded Desired Result" = Table.ExpandTableColumn(#"Grouped Rows", "Desired Result", {"TrainingStatus DateTime", "Training Status", "Desired Result"})
in
    #"Expanded Desired Result"

Hi bernate 

Here is your solution using DAX.

1. Create first calculated column using below dax.

LatestDateTime =

CALCULATE(

    MAX('Table'[TrainingStatus DateTime]),

    ALLEXCEPT('Table', 'Table'[Employee Name], 'Table'[Training])

)

 

2. Create second calculated column using below dax.

LatestStatus =

CALCULATE(

    MAX('Table'[Training Status]),

    FILTER(

        ALLEXCEPT('Table', 'Table'[Employee Name], 'Table'[Training]),

        'Table'[TrainingStatus DateTime] = [LatestDateTime]

    )

)

 

3. Create final calculated column uisng below dax.

Desired Result =

IF(

    [LatestStatus] = "Complete",

    "Training Complete",

    "In Training"

)

 

 

 

If this answers your questions, kindly accept it as a solution and give kudos