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" )

[DMB90]

Thank you, but for example, CBT-05 has one phase with Complete-Fail and Discussed with Mgmt, so I want every line that says CBT-05 to read as Ineffective or just use the Walkthrough phase row only to look at every phase for each control and put Effective/Ineffective on the walkthrough phase row (based on all phases) since every control has a walkthrough phase.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below image and the attached pbix file.

Or, please draw how the expected result looks like.

effective/ineffective CC =
VAR _condition =
    COUNTROWS (
        FILTER (
            data,
            data[control name] = EARLIER ( data[control name] )
                && data[status] = "complete-fail"
                && data[discussed] = "discussed with Mgmt"
        )
    ) >= 1
RETURN
    IF ( _condition, "ineffective", "effective" )

[DMB90]

This resolved it. Thank you!

[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" )

[DMB90]

I tried using the formula, but received an error.