TEXTCONTAINS

Applies to: Calculated column Calculated table Measure Visual calculation

Returns TRUE if text in a column matches a search string according to the specified full-text search mode. Otherwise, it returns FALSE.

Syntax

TEXTCONTAINS(<within_text_column>, <search_text>[, <match_mode>])

Parameters

Term Definition
within_text_column A column that contains the text values to search. The column must have the String data type.
search_text A text string that defines the search condition.
match_mode (Optional) The matching behavior to use. Supported values are TEXTMATCHING, FUZZYMATCHING, and PHRASEMATCHING. The default is TEXTMATCHING.

Return value

TRUE if the text in within_text_column matches search_text according to match_mode; otherwise, FALSE.

Match modes

Mode Behavior
TEXTMATCHING Tokenizes search_text and matches text that contains any of the search terms.
FUZZYMATCHING Matches tokens that are similar to search tokens within a small edit distance, which supports typo-tolerant matching.
PHRASEMATCHING Matches analyzed tokens that appear next to each other and in the specified order. Punctuation doesn't affect the token sequence.

Remarks

  • The search is case-insensitive.
  • The function applies language-specific stemming and stop-word removal based on the model culture. For example, stemming allows a search for run to match running, runs, and runner. Stop-word removal ignores common filler words like the, and, of, and in.
  • Per-column collation doesn't control the analyzer language. The model culture controls it. In Power BI Desktop, select File > Options and settings > Options > Current file > Regional settings > Model language to set the model language. For more information, see FORMAT.
  • The supported model cultures are:
    • English (en)
    • French (fr)
    • German (de)
    • Spanish (es)
    • Portuguese (pt)
    • Russian (ru)
    • Arabic (ar)
    • Turkish (tr)
    • Danish (da)
    • Dutch (nl)
    • Finnish (fi)
    • Greek (el)
    • Hungarian (hu)
    • Italian (it)
    • Norwegian (no, nb, and nn)
    • Romanian (ro)
    • Swedish (sv)
    • Tamil (ta)
  • For unsupported cultures, TEXTCONTAINS uses a generic lowercase tokenizer without stemming or stop-word removal.
  • Accent folding isn't supported. For example, cafĂ© doesn't match cafe.
  • The function requires a persisted full-text index on within_text_column. It's supported for Import, Dual, and Direct Lake storage modes. Other storage modes return an error.
  • The function is evaluated row by row. Before using it in a calculated column over a very large table, test the performance with a representative workload.
  • TEXTCONTAINS returns a Boolean value and isn't intended for relevance ranking. Use TEXTSIMILARITY to rank matches.

Examples

Match any search term

The following query returns products whose descriptions contain a token that matches either wireless or bluetooth:

EVALUATE
FILTER (
    Products,
    TEXTCONTAINS ( Products[Description], "wireless bluetooth" )
)
Example text Result
Wireless headphones with Bluetooth 5.0 TRUE
Wireless noise-cancelling headphones TRUE
Wired headphones FALSE

Match all search terms

Combine separate calls with the && operator to require all terms:

EVALUATE
FILTER (
    Reviews,
    TEXTCONTAINS ( Reviews[Comment], "battery" )
        && TEXTCONTAINS ( Reviews[Comment], "life" )
)
Example text Result
Battery life is amazing TRUE
Good life from this battery TRUE
Battery charges fast FALSE

Find a fuzzy match

The following query uses typo-tolerant matching to find comments that contain a token similar to headphone:

EVALUATE
FILTER (
    Reviews,
    TEXTCONTAINS ( Reviews[Comment], "headphone", FUZZYMATCHING )
)
Example text Result
These headphones are great TRUE
Best headfone I own TRUE
Excellent earbuds FALSE

Find a phrase match

The following query finds descriptions in which noise and cancel appear next to each other and in that order:

EVALUATE
FILTER (
    Products,
    TEXTCONTAINS ( Products[Description], "noise cancel", PHRASEMATCHING )
)
Example text Result
Noise cancel headphones pro TRUE
Cancel noise adapter FALSE

Related content

  • Last updated on