Add column based on another column values from another table

[Nnamarie_1997]

Hi,

Good day.

Asking for your help, I am only a beginner.

I am struggling to get the Status in Table 2 that excludes Process #1,

by using New column?

These are my 2 sample tables:

And my sample Desired Output:

Reference for my Status Column:

On-Going if the Group still have "Not Yet Done" Remarks, ignoring Process 1.

Completed If the Group have "Ignore/Done" Remarks, ignoring Process 1.

Thank you in advance ‌‌:)

[Jihwan_Kim · Accepted solution]

New Table1 =

ADDCOLUMNS (

VALUES ( Table1[Col 1] ),

"@status",

CALCULATE (

VAR currentcategory =

MAX ( Table1[Col 1] )

VAR withoutprocessone_withnotyetdone =

FILTER (

Table1,

Table1[Col 1] = currentcategory

&& Table1[Process] <> 1

&& Table1[Remarks] = "Not Yet Done"

)

RETURN

IF ( COUNTROWS ( withoutprocessone_withnotyetdone ) > 0, "OnGoing", "Complete" )

)

)

[Nnamarie_1997]

Thank You so much Sir..