CALCULATE conditional filters for specific circumstances

[Anonymous]

Lets say I had a table like below:

Action	Duration (minutes)	Evaluation	Factor A	Factor B
Running	20	Good	Yes
Jumping jacks	12	Bad	Yes
Running	19	Okay
Jumping jacks	11	Great		Yes
Jumping jacks	115	Superb		Yes
Running	412	Bad	Yes
Jumping jacks	0	Good		Yes

I have a DAX formula below that is meant to average the Duration field, excluding rows where the Evaluation is "Bad", Factor A is "Yes", and/or Factor B is "Yes."

Duration Average = CALCULATE(
AVERAGE('Table'[Duration (minutes)]),
'Table'[Evaluation] <> "Bad",
'Table'[Factor A] <> "Yes",
'Table'[Factor B] <> "Yes"
)

Lets say that for Jumping Jacks, I do want to include rows where Factor A is "Yes" and/or Factor B is "Yes." For running, I want to exclude rows where Factor A is "Yes" and/or Factor B is "Yes."

Is there a way that I can adjust this DAX formula so it only excludes Factor A and Factor B for "Running" rows?

It would be simple to write two separate DAX formulas, but my boss wants me to create a table that looks like this:

Action                                        Average Duration

Jumping jacks
Running

The only way I can figure out to have one column for average Durations is to have a single DAX average formula. 

Another alternative I thought of was using Power Query to remove running durations with Factor A and/or Factor B, but my boss does not want me to use Power Query to alter the table in that way.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your logic correctly, but please check the below picture and the attached pbix file.

Avg duration with the condition measure: =
AVERAGEX (
    FILTER (
        Data,
        SWITCH (
            TRUE (),
            Data[Action] = "Running",
                Data[Factor A] <> "Yes"
                    && Data[Factor B] <> "Yes",
            Data[Action] = "Jumping jacks",
                Data[Evaluation] <> "Bad"
                    && OR ( Data[Factor A] = "Yes", Data[Factor B] = "Yes" )
        )
    ),
    Data[Duration (minutes)]
)

[Anonymous]

Thank you!