Conditional Column on Hierarchy

Hi PBInewuser , 

You are using a few terms interchangably that are not the same thing.

A conditional column is a column in Power Query. I would generally recommend this is the way to go as you are changing the model with a new column, but if I am undersanding your question, you want it for any single contract number that has any status as failed, so Power Query isn't the way to go.

You are then asking for a new column, but then ask for a measure. I think you want a new column in a visual, not the model, and a measure is the way to go.

Final Status = 
VAR varFailedStatusCount =
    CALCULATE(
        COUNTROWS('Table'),
        REMOVEFILTERS('Table'[Date],'Table'[Status]),
        'Table'[Status] = "Failed"
    )
var Result = 
    IF(
        varFailedStatusCount > 0,
        "Failed",
        "Success"
    )
RETURN
    Result

That returns this:

Contract 1 is failed, but contract 2 has no falures, so the final status is Success.

My PBIX is here if you want to look at it.