topic Using CONTAINSSTRING function inside CALCULATETABLE to filter partial string matches to multiple str in DAX Commands and Tips

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

sumant28 — Fri, 28 Apr 2023 03:44:51 GMT

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

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

tamerj1 — Fri, 28 Apr 2023 03:54:50 GMT

Hi sumant28

you can do

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

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

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

sumant28 — Fri, 28 Apr 2023 04:09:54 GMT

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

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

tamerj1 — Fri, 28 Apr 2023 05:01:11 GMT

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.