Custom Column Based on Groups in 2 Other Columns

[bernate]

Hello, 

I am trying to create a custom column that would designate which employees are still "in training." My data looks like below:

Employee Name	Training	TrainingStatus DateTime	Training Status	Desired Result
A	Leadership	6/24/2025 11:00	Started	Training Complete
A	Leadership	6/24/2025 11:00	In Progress	Training Complete
A	Leadership	6/24/2025 11:10	Complete	Training Complete
A	Communication	6/24/2025 11:15	Started	In Training
A	Communication	6/24/2025 11:15	In Progress	In Training
B	Communication	6/24/2025 11:12	Started	Training Complete
B	Communication	6/24/2025 11:00	In Progress	Training Complete
B	Communication	6/24/2025 11:28	Complete	Training Complete
B	Leadership	6/24/2025 11:30	Started	Training Complete
B	Leadership	6/24/2025 11:30	In Progress	Training Complete
B	Leadership	6/24/2025 11:36	Complete	Training Complete
C	Leadership	6/24/2025 11:15	Started	In Training
C	Leadership	6/24/2025 11:15	In Progress	In Training
C	Communication	6/24/2025 11:25	Started	In Training
C	Communication	6/24/2025 11:25	In Progress	In Training

An employee can work on and complete multiple trainings and a training can have multiple employees at the same time. From this list, I want to know how many employees are still "In Training"- if the last timestamp of the training has status = "Started" or "In Progress" then "In Training" else if last timestamp of the training has status = "Complete" then "Training Complete."

If this can be done more easily in DAX then I am open to that as well. I have just been trying this in Power Query by grouping but I am stuck on how to loop through all of the employees and all of the trainings to get the result. Thanks!

[mdaatifraza5556]

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

[bernate]

Thank you for this alternate method. I ended up using the Power Query solution for efficiency since my table is well over 1 million rows.

[ronrsnfld]

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"

[bernate]

Thank you, this worked for me too. Is there a way to see the performance of a table in Power Query to determine if a methd is more efficient?

[ronrsnfld]

In the power query editor on power bi there is a diagnostic tools tab which lets you do that. When I checked the small amount of data in your sample executed in a quarter of the time as the accepted. But it's not always reliable. You should compare on your large data set.

[bernate]

Can the source data be changed to a dataflow instead of the JSON file? I keep getting an error that the column Employee Name of the table wasn't found, or I get a syntax error in Advanced Editor when I try to replace the Source as my dataflow.

[ronrsnfld]

The JSON document is merely the representation of who Power BI interprets the pasting of your data into the blank table.  Used as is there should be no errors (there are none here).

• If Employee Name is not found, it is probably named something else. (PQ is case-sensitive, and there may be spaces or some non-printing characters)
  • What is the first Step that shows the "not found" error?
• You should be able to easily replace Source with another Source. But without seeing what you actually did, I can't comment on your Syntax error.

[bernate]

If I try to use your soltion but replace the Source with my dataflow, I run into the error "Token Identifier expected" on the second "let" statement. I masked the workspace and dataflow IDs in the code below.

let
    Source = PowerPlatform.Dataflows(null),
    Workspaces = Source{[Id="Workspaces"]}[Data],
    #"workspace" = Workspaces{[workspaceId="workspace"]}[Data],
    #"dataflow" = #"workspace"{[dataflowId="dataflow"]}[Data],
    #"Current Employee Training Records_" = #"dataflow"{[entity="Current Employee Training Records",version=""]}[Data],
 let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, Training = _t, #"TrainingStatus DateTime" = _t, #"Training Status" = _t]),
    #"Renamed Columns" = Table.RenameColumns(#"Current Employee Training Records_",{{"Old Training", "Training"}, {"Old Employee Name", "Employee Name"}, {"Old Status Namee", "Training Status"}, {"Old Training Date", "TrainingStatus DateTime"}}),
    #"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"

[ronrsnfld]

Unless your data source code includes that second let, it looks like you only replaced part of the Source line from my code.

The Source line starts at Source = and ends at the comma before the #"Changed Type" step.

That second let is part of the Source statement.

[AlienSx · Accepted solution]

let
    status = [Complete = "Training Complete"],
    Source = your_data,
    group = Table.Group(
        Source, 
        {"Employee Name", "Training"}, 
        {
            {"rows", each _}, 
            {"status", (x) => Record.FieldOrDefault(status, Table.Max(x, "TrainingStatus DateTime")[Training Status], "In Training")}
        }
    ),
    result = Table.ExpandTableColumn(group, "rows", {"TrainingStatus DateTime", "Training Status"})
in
    result

[bernate]

Thank you, I ended up using your code as the solution.

[jgeddes]

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"

[bernate]

Thank you, this worked as well.