Create a new column based on conditions being met

[NPC]

Hi Everyone,

I have a table like this:

Item #	Code	Task
12345	7	ST
12345	8	CR
12345	9	RW
12345	10	CR
12346	3	ST
12346	4	CR

Basically the goal is to end up with another column (Open ST) that will only show open STs. Open STs are determined by NOT having Task RW anywhere after ST.

End goal:

Item #	Code	Task	Open ST
12345	7	ST
12345	8	CR
12345	9	RW
12345	10	CR
12346	3	ST	ST
12346	4	CR

So in this case Item # 12346 will end up with ST because there is no Task RW anywhere after Task ST. Item # 12345 won't be an open ST because RW is present somewhere after ST.

As you can see, other Tasks can be present after ST but that's not necessarily relevant as I care about the Tasks ST and RW.

Any thoughts on how I can code this. 

Thanks in advance!

[NPC]

@Greg_Deckler    thank you. I haven't tried your solution because I found one that works already. I'm confident it will work too. Thanks for taking the time to work on this. 

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

Open ST = if(Data[Task]="ST",if(CALCULATE(COUNTROWS(Data),FILTER(data,Data[Item #]=EARLIER(Data[Item #])&&Data[Code]>EARLIER(Data[Code])&&Data[Task]="RW"))>0,BLANK(),Data[Task]),BLANK())

Hope this helps.

[NPC]

@Ashish_Mathur , thank you! This works. Really appreaciate taking the time. 

[Ashish_Mathur]

You are welcome.

[CNENFRNL]

For fun only, a showcase of powerful Excel worksheet formula,

[hnguy71]

@NPC,

Can you see if this fits your criteria?

Open ST = 

VAR _GetAllTasks = CALCULATE(CONCATENATEX(NPC, [Task], "|"), FILTER(NPC, NPC[Item #] = EARLIER(NPC[Item #])))
VAR _Exclude = "RW"
VAR _Include = "ST"

RETURN

SWITCH( TRUE(),
    SEARCH(_Exclude, _GetAllTasks, 1, 0) > 0, BLANK(),
    SEARCH(_Include, _GetAllTasks, 1, 0) > 0 && [Task] = _Include, _Include
)

[NPC]

@hnguy71 thank you. I haven't tried your solution because I found one that works already but all indications are that this would work too. Thanks for taking the time to work on this. 

[Greg_Deckler]

@NPC Try:

Open ST = 
    VAR __Item = [Item #]
    VAR __Code = [Code]
    VAR __Table = FILTER('Table',[Code] > __Code && [Item #] = __Item && [Task] = "RW")
RETURN
    IF([Task] = "ST" && COUNTROWS(__Table)<1, "ST", BLANK())