Getting rows with the same value

[bunny53]

Hi, I need some help with getting a final status per each ID. The table is sorted by time, and IDs are composed of random alphabets and numbers. Currently a final status is in a string format like below. I created a look up table for each status, converting each status to a numerical value as I wished to be prioritized. What I want is, if operations for an ID has at least one "Complete", I want the table to say "yes", and otherwise(no Complete at all) "no". For example of ID "K304R" below, it operated three times with Status of "Completed", "Error", and "Canceled", and thus the result I want would be a "yes".

My intuition was 1) ALLEXCEPT original table with ID and Status, 2) somehow get rows with the same ID(ex "K304R"), 3) somehow get Status for each rows of "K304R", 4) somehow connect Status back to the look up table, 4) get Max value for statuses, 5) return "yes" if the max value is 100, and otherwise "no".

Any help would be really appreciated. Thanks ahead!

OriginalTable

Time	ID	Status
2022/10/4 10:47AM	1ZT56	Error
2022/10/4 9:47AM	K304R	Completed
2022/10/4 7:47AM	K304R	Canceled
2022/10/3 10:47PM	1ZT56	Completed
2022/10/3 7:47AM	PQ534	Canceled
2022/10/3 4:47AM	12PT3	Error
2022/10/2 10:40PM	12PT3	Error
2022/10/2 7:47PM	1ZT56	Canceled
2022/10/1 10:47AM	U73RL	Completed

LookupTable

Status	StatusVal
Completed	100
Canceled	0
Error	0

Result I want

Time	ID	Status	FinalStatus
2022/10/4 10:47AM	1ZT56	Error	yes
2022/10/4 9:47AM	K304R	Completed	yes
2022/10/4 7:47AM	K304R	Canceled	yes
2022/10/3 10:47PM	1ZT56	Completed	yes
2022/10/3 7:47AM	PQ534	Canceled	no
2022/10/3 4:47AM	12PT3	Error	no
2022/10/2 10:40PM	12PT3	Error	no
2022/10/2 7:47PM	1ZT56	Canceled	yes
2022/10/1 10:47AM	U73RL	Completed	yes

[MarkLaf · Accepted solution]

Are you actually looking to leverage LookupTable for other reasons or is its only use to try to get the correct FinalStatus output?

It's actually easier to do this without a helper table. You were on the right track with ALLEXCEPT, but can skip a bunch of the steps you were envisioning by using CONTAINS:

FinalStatus =
CALCULATE(
    IF(
        CONTAINS( OriginalTable, OriginalTable[Status], "Completed" ),
        "yes",
        "no"
    ),
    ALLEXCEPT( OriginalTable, OriginalTable[ID] )
)