CALCULATE conditional filters for specific circumstances

Anonymous — Tue, 28 Jun 2022 00:47:48 GMT

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.

Re: CALCULATE conditional filters for specific circumstances

Jihwan_Kim — Tue, 28 Jun 2022 03:22:15 GMT

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

Re: CALCULATE conditional filters for specific circumstances

Anonymous — Tue, 28 Jun 2022 13:34:07 GMT

Thank you!

topic Re: CALCULATE conditional filters for specific circumstances in DAX Commands and Tips

CALCULATE conditional filters for specific circumstances

Re: CALCULATE conditional filters for specific circumstances

Re: CALCULATE conditional filters for specific circumstances