If Any Row with a Specific ID contains "Closed" then Return "Closed" Value in All Rows for ID

[DMB90]

I have a column with a specific ID listed several times and I'm trying to check if any row with a specific ID contains a "Closed" status and if so mark it as "Closed" but if all rows with a specific ID are blank in status, then mark it as "Open" in the newly created column.

[v-kongfanf-msft · Accepted solution]

Hi @DMB90 ,

You can try below formula to create calculated column:

Status Result = 
VAR CurrentID = 'YourTable'[ID]
VAR ClosedCheck = 
    CALCULATE(
        COUNTROWS('YourTable'),
        FILTER(
            'YourTable',
            'YourTable'[ID] = CurrentID &&
            'YourTable'[Status] = "Closed"
        )
    )
RETURN
    IF(
        ClosedCheck > 0,
        "Closed",
        "Open"
    )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DMB90]

The column for ID I need is already a measure and won't register. It's called "commentable_id" and is a measure and set to whole numbers.