Forum Discussion

Krcmajster's avatar
Krcmajster
Helper IV
4 years ago
Solved

CONTAINSSTING with multiple values from different column

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] ) ) )
))



Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.