Generate a new table with set of key words

[Anonymous]

Hi all,

I'm trying to generate a new table by filtering for some keywords in my old table.  I tried using filter but it doesn't work.

This is the measure I tried:

New_Table = FILTER(Old_Table,OR(OR(OR(CONTAINS(Old_Table,Old_Table[Letter],"A),CONTAINS(Old_Table,Old_Table[Letter],"B")),CONTAINS(Old_Table,Old_Table[Letter],"C")), CONTAINS(Old_Table,Old_Table[Letter],"D")))

I have also tried

New_Table = CALCULATE([Calculation], FILTER(Old_Table,OR(OR(OR(CONTAINS(Old_Table,Old_Table[Letter],"A),CONTAINS(Old_Table,Old_Table[Letter],"B")),CONTAINS(Old_Table,Old_Table[Letter],"C")), CONTAINS(Old_Table,Old_Table[Letter],"D"))))

Which did not work either.

Any help would be highly appreciated!

[v-juanli-msft]

Hi @Anonymous 

Enter the formula in the Modeling->New table

Table 2 = FILTER('Table','Table'[letter] IN {"A","B","C","D"})

orignal table

letter

A

B

C

D

Q

E

R

T

Y

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, thanks for your reply.  I tried your solution, however it's returning me an empty table.  Does it have to do with the fact that my table only contains the keyworks but are not exactly the key words? (i.e. the table may have "apple", "aloe", etc. and I want to find everything with an a)

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

You can create a column

Column 2 = SEARCH("a",[column],1,0)>0||SEARCH("b",[column],1,0)||SEARCH("c",[column],1,0)||SEARCH("d",[column],1,0)

Or you could create a new table(column2 is not created)

Table 2 =
FILTER (
    ADDCOLUMNS (
        'Table',
        "Column 3", SEARCH ( "a", [column], 1, 0 ) > 0
            || SEARCH ( "b", [column], 1, 0 )
            || SEARCH ( "c", [column], 1, 0 )
            || SEARCH ( "d", [column], 1, 0 )
    ),
    [Column 3] = TRUE ()
)

Or create a measure

Measure =
IF (
    SEARCH ( "a", MAX ( 'Table'[column] ), 1, 0 ) > 0
        || SEARCH ( "b", MAX ( 'Table'[column] ), 1, 0 )
        || SEARCH ( "c", MAX ( 'Table'[column] ), 1, 0 )
        || SEARCH ( "d", MAX ( 'Table'[column] ), 1, 0 ),
    1
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.