SEARCH FUNCTION WITH A LIST VALUE

[v-lili6-msft]

Scenario:  

Currently, we have 2 lists. Now we want to check if list 1 contains any value from list 2. How will we achieve this requirement? Please see below for details.

Sample Data             

Now use this logic to create a calculated column in 'Dim Name' table:

if value in list exists in current value ? =

   IF(

      SUMX(List,

           SEARCH(

                List[Keyword],

               'Messy names'[Name]

                ,1,0

               )

          ) > 0,

      "YES",

      "No"

     )

for each row in List, evaluate the SEARCH function. 

Result:

By the way, you could also use this step ( SUMX ( List, SEARCH ( List[Keyword], 'Messy names'[Name], 1, 0 ) ) > 0 ) as a conditional in another formula, such as:

Measure =

CALCULATE (

    COUNTROWS ( 'Messy names' ),

    FILTER (

        'Messy names',

        SUMX ( List, SEARCH ( List[Keyword], 'Messy names'[Name], 1, 0 ) ) > 0

    )

)

Use this logic to determine which list values in list are contained in name column

which values in list are in current value? =

CONCATENATEX (

    CALCULATETABLE (

        VALUES ( List[Keyword] ),

        FILTER ( List, SEARCH ( List[Keyword], 'Messy names'[Name], 1, 0 ) > 0 )

    ),

    [Keyword],

    ","

)

Another way is that you could do it by the same logic in power query.

In power query, you’d better divide it into several steps as below:

    #"Added Custom" = Table.AddColumn(#"Changed Type", "ListKeyword", each List[Keyword]),

    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "ListKeyword"),

    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "IsContains?", each if Text.Contains([Name], [ListKeyword]) = true then 1 else 0),

    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Name"}, {{"Result", each if List.Sum([#"IsContains?"])>0 then "Yes" else "No", type number}}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"Result", type text}})

in

#"Changed Type1"

Let me explain the processes.

#"Added Custom" //    Convert Keyword column into a list and then add it as costom column.

#"Expanded Custom"//  Expand above list column so that each list value will join with basic table

#"Added Custom1"//    Add a custom column to judge if this list value is contained in current value.

#"Grouped Rows"//     Summary results just like SUMX in dax formula.

Result:

In additional, for SEARCH and FIND function, Search is case-insensitive and accent sensitive, but FIND is case-sensitive.

For example, there is a difference between the above expression if you use “A” or “a” in the FIND, there is no difference between “A” or “a” when you use the Search function.

Use Find = FIND("A",'Messy names'[Name],1,0)

Use Search = SEARCH("A",'Messy names'[Name],1,0)

For first row, FIND will only return first index for “A”, it is 10, but SEARCH will return first index for “A” or “a”.

And you can easily change FIND or SEARCH to return exactly the same result too. meaning that you can make FIND not case sensitive. here is an example of how you can make FIND not case sensitive:

Use Find with UPPER = FIND(UPPER("A"),UPPER('Messy names'[Name]),1,0)

The above expression is using UPPER to make the <find_text> and <within_text> all uppercase, and then compare them, or you can do lowercase, it has the same logic.

Author: Lin Li

Reviewer: Yuyang

DEMO.pbix