Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX FILTER with multiple criteria

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...
  • ValtteriN's avatar
    ValtteriN
    4 years ago

    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!
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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" }
)
ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
4 years ago

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!
StoryofData's avatar
StoryofData
Icon for Helper III rankHelper III
3 years ago

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"

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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