cancel
Showing results for
Did you mean:  Helper I

## CALCULATE conditional filters for specific circumstances

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'[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.

1 ACCEPTED SOLUTION  Super User

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",
&& OR ( Data[Factor A] = "Yes", Data[Factor B] = "Yes" )
)
),
Data[Duration (minutes)]
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

2 REPLIES 2  Super User

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",
&& OR ( Data[Factor A] = "Yes", Data[Factor B] = "Yes" )
)
),
Data[Duration (minutes)]
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.  Helper I

Thank you! Announcements #### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates. #### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!  