Forum Discussion

sumant28's avatar
sumant28
New Member
3 years ago

Using CONTAINSSTRING function inside CALCULATETABLE to filter partial string matches to multiple str

table =
DATATABLE (
    "Name", STRING,
    "Code", STRING,
    "ID", INTEGER,
    {
        { "asldkjfaaa", "ABC", 1 },
        { "jdfkbbb", "ABC", 2 },
        { "dskhgaaa", "XYZ", 3 },
        { "hfdjk", "ABC", 4 },
        { "eowyyui", "ABC", 5 }
    }
)

filter =
CALCULATETABLE (
    'table',
    'table'[Code] = "ABC",
    FILTER ( 'table', NOT ( CONTAINSSTRING ( 'table'[Name], "aaa" ) ) )
)

 

I am using powerbi and I am selecting the New table button first to create the data and second to filter the data. What I am tripping over is the fact that CONTAINSSTRING only accepts two arguments and the second argument has to be a string rather than the option of multiple strings. Moreover I cannot repeat the function argument line in the CALCULATETABLE list of filters. The only solution I have now is to wrap the entire code block inside a FILTER argument for each partial string match I want to exclude. I am wondering if there is something better

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion

    Hi sumant28 

    you can do

    filter =
    CALCULATETABLE (
    'table',
    'table'[Code] = "ABC",
    NOT ( CONTAINSSTRING ( 'table'[Name], "aaa" ) )
    )

     

    or

     

    filter =
    FILTER (
    'table',
    'table'[Code] = "ABC"
    && NOT ( CONTAINSSTRING ( 'table'[Name], "aaa" ) )
    )

    • sumant28's avatar
      sumant28
      New Member

      I forgot to mention in my original post that I don't just want to filter out a partial match to "aaa" but also to "bbb" and so on. My actual problem includes a list of about six such strings. I can have my query take the form of something like 

      FILTER(FILTER(FILTER....

      But I am not satisfied with how that solution looks

      • tamerj1's avatar
        tamerj1
        Community Champion

        sumant28 

        Definitely you need to iterate ove that list but that is fine, ot is just a small list of 6 rows. For sure there will be room for optimization but you need to be specific about what exactly are you trying to accomplish.