How to calculate column based on like data in one column compared to a second column

[Anonymous]

I am not sure how to word this problem so but I'll do my best.

I have a column that lists names, courses they have taken, and their status of said course. I need a column that states if they have just Completed the final course (Certified), are in progress, or have not started. I have tried a conditional column, but that does not do what I need. It will show in the certified table that they have completed the certification, but they also show up in the in progress table because they have also the course

Example data:

Name       Training Title        Transcript Status

Jerry         Example Cert       Completed

Jerry         Example Class1    Completed

Jerry         Example Class2    Completed

Jane          Example Cert        In Progress

Jane          Example Class      Completed

Kevin         Example Class       Not Started

Spencer     Example Cert       Not Started

Spencer      Example Class      In Progress

What I want to add is a 4th column, showing if they are certified in a specific class. In this case, I want to know if they have either completed the Example Cert, are in progress for it, or have not started (everyone else), and for the highest status to fill down to the rest of the columns

Name       Training Title        Transcript Status    Cert Status 

Jerry         Example Cert       Completed             Started

Jerry         Example Class1    Completed             Started

Jerry         Example Class2    Completed             Started

Jane          Example Cert        In Progress            In Progress

Jane          Example Class1      Completed           In Progress

Kevin         Example Class3       Not Started         Not Started

Spencer     Example Cert       Not Started           In Progress

Spencer      Example Class1      In Progress         In Progress

How could I do this?

[Anonymous · Accepted solution]

I figued it out (thanks to @amitchandak for the base code!)

The return Switch() statement did not function as intended. Instead, I used this:

Status =
var _cnt = countx(filter(Table, [Name] = earlier([Name])), [Name])
var _cntc = countx(filter(Table, [Name] = earlier([Name]) && [Transcript Status] = "Completed"), [Name])
var _cntp = countx(filter(Table, [Name] = earlier([Name]) && [Transcript Status] = "In Progress"), [Name])
return

Switch(true() , 

_cntc > 0, "Completed", 
_cntp > 0 && _cntc < 1, "In Progress", 
"Not Started" ) 

And it worked perfectly!

[amitchandak]

@Anonymous , Try a new column like

new column =
var _cnt = countx(filter(Table, [Name] = earlier([Name])), [Name])
var _cntc = countx(filter(Table, [Name] = earlier([Name]) && [Transcript Status] = "Completed"), [Name])
var _cntp = countx(filter(Table, [Name] = earlier([Name]) && [Transcript Status] = "In Progress"), [Name])
return
Switch(true() ,
_cnt = _cntc, "Started",
not(isblank(_cntp)) , "In Progress",
"Not Started"
)

[Anonymous]

Thank you for the quick reply!

I tried that and it only seems to work with whatever the first row is. Is there a way to get it so if it ever encounters a higher level (i.e. Not Started -> In progress or In Progress -> Completed or Not Started -> Completed), it sets everything to be that?