Forum Discussion

DorienM's avatar
DorienM
Icon for Helper II rankHelper II
4 years ago
Solved

How can I include multiple strings in a "Does not contain" filter?

I have a table that I need to filter out rows where 'Table1'[Name] does not contain one of many strings, this is easy to do if I only have two potential strings but in my case I have upwards of 10+ strings I need to filter out. I would prefer to do this on the report itself using visual level filters rather than in Power Query if possible.

 

As an example I need to exclude rows in the table below that contain one of "TEST", "DEV, or "TRAINING".

Name
3042-TEST
VDI-2291
WIN10-DEV1
KEVIN
TRAINING-21
TRAINING-22
TRAINING-23
DC-CGY1
SQLSVR1

 

This should return these rows only:

Name
VDI-2291
KEVIN
DC-CGY1
SQLSVR1
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    DorienM  you can use the following measure

    Measure =
CALCULATE (
    COUNTROWS ( t1 ),
    FILTER (
        t1,
        CONTAINSSTRING ( t1[Name], "TEST" ) = FALSE ()
            && CONTAINSSTRING ( t1[Name], "DEV" ) = FALSE ()
            && CONTAINSSTRING ( t1[Name], "TRAINING" ) = FALSE ()
    )
)

     

     

3 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    DorienM  you can use the following measure

    Measure =
CALCULATE (
    COUNTROWS ( t1 ),
    FILTER (
        t1,
        CONTAINSSTRING ( t1[Name], "TEST" ) = FALSE ()
            && CONTAINSSTRING ( t1[Name], "DEV" ) = FALSE ()
            && CONTAINSSTRING ( t1[Name], "TRAINING" ) = FALSE ()
    )
)

     

     

    • Aeradia's avatar
      Aeradia
      New Member
      2 years ago

       

      If you don't want to use a measure, the following youtube video helped me, it seems the native UI can do this if you trick it slightly!

https://www.youtube.com/watch?v=olZCiwvrEGs