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

[sumant28]

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

[tamerj1]

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]

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]

@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.