Need help with multiple filter values in DAX!

[k3rz0rg]

Hi everyone,

I've been trying to do the following codes in a DAX formula, however, it is not working as it does via applying the filters on filter pane. I have Stage column with value from A to E and Other. I needed to exclude the A and Other which in sql, I'd do using NOT IN { } but here I tried several methods but it is not working, it uses only one value and field (either A or Other, not both), 
also tried Filter ('data', NOT('data'[Stage]) IN {"A","Other"} ) but no luck.
Does anyone have any idea how can I exclude those 2 or 3 values from a field in this calculation?

var _Calc2 = CALCULATE(
                                SUM('data'[Credit])
                                ,'data'[Field 1] = "Spend Management"
                                ,'data'[Type] = "New"
                                ,'data'[Stage] = "A"
                                ,'data'[Stage] = "Other"
                                ,'data'[Code] <> "1231"
                                )

 
Thanks!

[sanc_152 · Accepted solution]

Hi @k3rz0rg 

try with this one ..

Measure 3 =

VAR SUMMRIZE_

       =CALCULATETABLE(

        'DATA',

        not( 'DATA'[Stage] in{ "A", "Other"})

       )

  VAR  Result=

 CALCULATE(

         SUMX(

             FILTER(SUMMRIZE_,

                            [Field 1]="Spend Management"&&

                            [Type] = "New"&&

                            [Code] <> 1231

             ),

       [Credit]

            )

 )

RETURN Result
Note: If this post helps mark this one as accepted.

[k3rz0rg]

Although this was marked as Solution randomly by default, however, it didn't work for me. While IN{ } works for the int values, it does not work for the texts in Power BI I found and had to create multiple variable for each IN filter and add those variables up in the return. Makes the code longer but at least works.

Thanks for the response though, I learned something new through that.

[Ashish_Mathur]

Hi,

Does this measure work?

var _Calc2 = CALCULATE(SUM('data'[Credit]),'data'[Field 1] = "Spend Management"&&'data'[Type] = "New"&&('data'[Stage] = "A"||'data'[Stage] = "Other")&&'data'[Code] <> "1231")

[amitchandak]

@k3rz0rg , try like

var _Calc2 = CALCULATE(
SUM('data'[Credit])
Filter( 'data', 'data'[Field 1] = "Spend Management"
&& 'data'[Type] = "New"
&& 'data'[Code] <> "1231" && not( ,'data'[Stage] in{ "A", "Other"})
) )

[k3rz0rg]

Thank you for the response, unfortunately I've already tried the FILTER() and tried your method as well but it is not considering multiple values inside IN { } , it only filters based on one value (in this case A between A and Other) no matter how many values I put inside the curly braces.