Forum Discussion

k3rz0rg's avatar
k3rz0rg
Advocate III
3 years ago
Solved

Need help with multiple filter values in DAX!

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!

Need Help
  • sanc_152's avatar
    sanc_152
    3 years ago

    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.

5 Replies

  • sanc_152's avatar
    sanc_152
    Helper I
    3 years ago

    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's avatar
      k3rz0rg
      Advocate III
      3 years ago

      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.

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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's avatar
      k3rz0rg
      Advocate III
      3 years ago

      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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

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