Huge Number of Phrases to Search For

[thecelerygod]

Hi Everyone,

I'm struggling with the best way to approach this. I've got a text column that I need to search for unique phrases and assign a tag to it (there's 22 tags to assign). For each tag, I've got 20 to 40 phrases to search for.

I've thought about something like this:

Column =
SWITCH(TRUE(),
CONTAINSSTRING([TextColumn], "phrase 1"), "tag 1",
CONTAINSSTRING([TextColumn], "phrase 2"), "tag 1",
CONTAINSSTRING([TextColumn], "phrase 3"), "tag 1",
CONTAINSSTRING([TextColumn], "phrase 4"), "tag 1",
CONTAINSSTRING([TextColumn], "phrase 5"), "tag 1",
CONTAINSSTRING([TextColumn], "phrase 6"), "tag 2",
CONTAINSSTRING([TextColumn], "phrase 7"), "tag 2",
CONTAINSSTRING([TextColumn], "phrase 8"), "tag 2",
)

But I'm not sure this is the best approach. Would anyone have a recommendation? Thanks!

[Greg_Deckler · Accepted solution]

@thecelerygod Create a table of your tags and phrases. Then you could write DAX like this:

Column = 
  VAR __Text = [Text Column]
  VAR __Table = 
    ADDCOLUMN(
      'Phrases',
      "Found", CONTAINSTRING( __Text, [Phrase] )
    )
  VAR __Result = CONCATENATEX( FILTER( __Table, [Found] = TRUE() ), [Tag], ", " )
RETURN
  __Result

[thecelerygod]

That's fantastic. Thanks!