Forum Discussion
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
- sumant28New 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