CONTAINSSTING with multiple values from different column

[Krcmajster]

I am trying to use column from one table as a slicer, to filter different table. Filter should be check if the value contains any of the selected values.

I wanted to use count to filter if the count is greater or equal to one. 

I have the following measure that works if single value is selected but I am struguling to find a way to solve it when multiple values are selected:

CALCULATE (
    COUNT ( Data[Countries] ),
    FILTER ( Data, CONTAINSSTRING ( Data[Countries], VALUES( ( Slicer[Country] ) ) )
))

[v-eqin-msft · Accepted solution]

Hi @Krcmajster ,

Agree with @amitchandak .

Please transform the table to:

Here is the whole M syntax

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvR1DfJ0dgzWUXD1dXVUitUBCgU4OmPl6ijAVIMl4CqgcmB1CBWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Countries = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Countries", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Countries", "Countries - Copy"),
    #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Countries - Copy", "Country"}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"Country", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Country"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Country", Text.Trim, type text}})
in
    #"Trimmed Text"

Then createa measures:

If contains = IF(MAX('Data'[Country]) in ALLSELECTED(Slicer[Country]),1,0)

Filter = 
var _t=ADDCOLUMNS('Data',"Flag",[If contains])
return SUMX(FILTER(_t,[Countries]=MAX('Data'[Countries])),[Flag])

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Krcmajster , I doubt searching multiple values as a table. Better to split the country in the table into multiple rows in power query