DAX Formula - Finding Any Row in a Column that contains a certain value

[DMB90]

I have a table in Power BI that contains the same values multiple times in a column, but I want to create a formula that says if any row in the column contains the same Control Name, and Status is equal to "Complete-Fail" and Discussed is equal to "Discussed with Mgmt" then make my new Effective/Ineffective column say "Ineffective", if not then "Effective" is returned. Keep in mind, every control will not have the same number of phases and not everything with a "Complete-Fail" status is Discussed with Mgmt. If you took, my sample data, I would only expect to see "Ineffective" for rows that say "Complete-Fail" and "Discussed with Mgmt."

Every control will have a Walkthrough phase while the other phases for each control will vary, so perhaps a formula can be built around if the control contains string "Complete" for the Walkthrough phase of a control, to find just one for that control that may contain "Complete-Fail" and "Discussed with Mgmt" for the Ineffective status. 

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a calcualted column.

effective/ineffective CC = 
VAR _condition = data[status] = "complete-fail"
    && data[discussed] = "discussed with Mgmt"
RETURN
    IF ( _condition, "ineffective", "effective" )

[Deku]

var controlName = datum[Control Name]
var ineffective = 
FILTER(
   ALL( datum ),
   datum[Control Name] =  controlName 
   && datum[Status] =     "Complete-Fail"
   && datum[Discussed] =  "Discussed with Mgmt"
)
return
IF( ISEMPTY( ineffective ), "Effective", "Ineffective" )