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

8 Replies

  • 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

    • dlcollins's avatar
      dlcollins
      Regular Visitor
      1 year ago

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

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    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" }
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      BGB
      Icon for Helper II rankHelper II
      3 years ago

      Keep Filters works for me everytime.