topic Re: DAX FILTER with multiple criteria in DAX Commands and Tips

DAX FILTER with multiple criteria

Anonymous — Wed, 22 Dec 2021 21:43:55 GMT

Hi everyone,

I really need help here. I need to calculate a measure and for doing so need to apply multiple filters to obtain the desired value.

I already tried some options suggested in this forum like the ones appointed by @amitchandak in this previous post https://community.powerbi.com/t5/Desktop/Filter-data-based-on-multiple-criteria-in-same-column/m-p/2057349#M768659 , but for some reason, my DAX doesn't work.

Here is the DAX I'm using:

Back Charge Int.Cost =

CALCULATE(

SUM('Back Charge Data'[Back Charge Cost]),

FILTER('Back Charge Data','Back Charge Data'[OPL] in {"CECO", "METALLIC", "STAR"}),

FILTER('Back Charge Data','Back Charge Data'[Selling Brand] in {"Drafting", "Engineering"})

)

I also tried this version:

Back Charge Int.Cost =

CALCULATE(

SUM('Back Charge Data'[Back Charge Cost]),

FILTER('Back Charge Data','Back Charge Data'[OPL] in {"CECO", "METALLIC", "STAR"} && 'Back Charge Data'[Selling Brand] in {"Drafting", "Engineering"}),

)

Help!

Re: DAX FILTER with multiple criteria

ValtteriN — Wed, 22 Dec 2021 22:33:59 GMT

Hi,

Calculate has a built in [filter] places in its expression and thus you don't need to add FILTER to your calculation. Something like this should work:

Back Charge Int.Cost =

CALCULATE(

SUM('Back Charge Data'[Back Charge Cost]),
all('Back Charge Data'),

'Back Charge Data'[OPL] in {"CECO", "METALLIC", "STAR"},

'Back Charge Data'[Selling Brand] in {"Drafting", "Engineering"}

)

Here I added ALL to remove other filters affecting the calculation.

I hope this helps to solve your issue and if it does consider accepting this post as a solution and giving it a thumbs up!

Re: DAX FILTER with multiple criteria

AlexisOlson — Wed, 22 Dec 2021 22:53:46 GMT

I don't see anything necessarily wrong with your DAX although it would be a bit more efficient to write it like this:

Back Charge Int.Cost = CALCULATE ( SUM ( 'Back Charge Data'[Back Charge Cost] ), KEEPFILTERS ( 'Back Charge Data'[OPL] IN { "CECO", "METALLIC", "STAR" } ), KEEPFILTERS ( 'Back Charge Data'[Selling Brand] IN { "Drafting", "Engineering" } ) )

Can you explain what you mean by "my DAX doesn't work"? Are you getting an error? Are you expecting it to act differently?

Are you looking for a version that replaces local filters rather than adding to them like this?

Back Charge Int.Cost = CALCULATE ( SUM ( 'Back Charge Data'[Back Charge Cost] ), 'Back Charge Data'[OPL] IN { "CECO", "METALLIC", "STAR" }, 'Back Charge Data'[Selling Brand] IN { "Drafting", "Engineering" } )

Re: DAX FILTER with multiple criteria

v-robertq-msft — Mon, 27 Dec 2021 02:58:57 GMT

Hi,

Have you followed the DAX formula posted by ValtteriN to find the solution to your problem?

If so, would you like to mark his reply as a solution so that others can learn from it too?

Thanks in advance!

How to Get Your Question Answered Quickly

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DAX FILTER with multiple criteria

Anonymous — Mon, 10 Jan 2022 17:01:40 GMT

With some work, I realized that the problem was in the data, not in the used DAX, but thanks for the improvement

Re: DAX FILTER with multiple criteria

StoryofData — Sat, 25 Feb 2023 16:26:45 GMT

How would I add on to this a condition that excludes a value? For example:
'Back Charge Data'[Selling Brand] DOES NOT INCLUDE "Drafting" AND "Engineering"

Re: DAX FILTER with multiple criteria

Anonymous — Mon, 27 Feb 2023 05:00:50 GMT

Hi , just add a NOT in the starting of the Filter,

Back Charge Int.Cost = CALCULATE( SUM('Back Charge Data'[Back Charge Cost]), all('Back Charge Data'), NOT('Back Charge Data'[OPL] in {"CECO", "METALLIC", "STAR"}), NOT('Back Charge Data'[Selling Brand] in {"Drafting", "Engineering"}) )

StoryofData

Re: DAX FILTER with multiple criteria

BGB — Fri, 31 Mar 2023 11:12:26 GMT

Keep Filters works for me everytime.

Re: DAX FILTER with multiple criteria

dlcollins — Fri, 05 Sep 2025 10:34:04 GMT

Thank you ValtteriN for explaining filtering within CALCULATE so efficiently. My DAX is now cleaner.