DAX FILTER with multiple criteria

[Anonymous]

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/2... , 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!

[ValtteriN · Accepted solution]

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!

[v-robertq-msft]

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.

[BGB]

Keep Filters works for me everytime.

[AlexisOlson · Accepted solution]

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

[Anonymous]

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

[ValtteriN · Accepted solution]

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!

[dlcollins]

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

[StoryofData]

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"

[HassanAshas]

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