Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter out multiple value

Hi Guys   Any easier way to filter multi value in the same column in dax? I have more than 20 values that consist of %CSH% and %CRT% which i want to filter out. Currently I'm using Calculate(Count)...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Anonymous ,

     

    You could try to add filter condition like this:  && NOT ( 'Table'[Column] IN { 1000005,1000006,1000007 } )

     

    My sample:

     

    Measure 2 =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER ( 'Table', NOT ( 'Table'[Product] IN { "Paseo", "Velo", "VTT" } ) )
)

     

     

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous , You need to use search find or containsstring

 

Like

FILTER(TableA,search("CSH",TableA[ColumnA],,0)<=0 && search("CRT",TableA[ColumnA],,0)<=0)

 

Here =0 means it does not have that string

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks! I have another question. in SQL where have  where IN and NOT IN operator, how can it apply it dax? if i want to exclude below 1000005,1000006,1000007  in my filter. Appreciate your help!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks! I have another question. in SQL they have where IN and NOT IN operator, how can we apply it in dax? if I want to exclude below 1000005,1000006,1000007  in my filter. Appreciate your help!

     

    ** Sorry for the previous message typo.

    • v-xuding-msft's avatar
      v-xuding-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Anonymous ,

       

      You could try to add filter condition like this:  && NOT ( 'Table'[Column] IN { 1000005,1000006,1000007 } )

       

      My sample:

       

      Measure 2 =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER ( 'Table', NOT ( 'Table'[Product] IN { "Paseo", "Velo", "VTT" } ) )
)

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi XueDing,

         

        Thanks alot! Cheers!