Power BI DAX Full-Text Search: TEXTCONTAINS and More

Navy Solv Systems title card reading 'New DAX: Full-Text Search and More' with a checklist motif.

In Short: Power BI Full-Text Search Arrives in DAX

Power BI semantic models have always been weak at text. Searching customer comments or support tickets meant SEARCH or CONTAINSSTRING scanning row by row, with no idea that "running" and "run" are the same word. Microsoft's September 2026 feature summary changes that with full-text search in DAX: two new functions, TEXTCONTAINS and TEXTSIMILARITY, backed by a new full-text index on the column.

The same release brought APPROXIMATEDISTINCTCOUNT to Import and Direct Lake models, and String Indexing to speed up existing text functions. A month earlier, Direct Lake finally got calculated columns. All four are preview, so treat this as a guide to what to test, not what to ship. If DAX itself is new to you, start with what DAX is.

TEXTCONTAINS: Search That Understands Words

TEXTCONTAINS returns TRUE when text in a column matches a search string. The difference from CONTAINSSTRING is how it matches. Microsoft's reference page lists the behaviour:

Case-insensitive, with stemming (a search for run matches running, runs and runner) and stop-word removal (the, and, of)
Language rules come from the model culture, not the column collation. English, French, German, Spanish, Dutch, Italian and a dozen more are supported; other cultures fall back to a plain lowercase tokeniser
No accent folding, so café does not match cafe
Three match modes: TEXTMATCHING (any of the terms, the default), FUZZYMATCHING (tolerates small typos, so "headfone" finds headphone) and PHRASEMATCHING (terms adjacent and in order)

One trap: in the default mode, "battery life" matches text containing either word. To require both, Microsoft's own example combines two TEXTCONTAINS calls with &&.

Where this earns its keep is any model with free text that people want to filter on: reviews, complaint notes, incident summaries, product descriptions. Today those questions often end in an export to Excel. With a full-text index they can be a measure or a slicer.

TEXTSIMILARITY: Ranking, Not Just Matching

TEXTSIMILARITY takes the same arguments but returns a score. Higher means a stronger match, 0 means no match, so you can ask for "the ten reviews most relevant to good breakfast" with TOPN.

Read Microsoft's caveats carefully. The reference page calls it a lexical relevance score, and says it is opaque: not a percentage or probability, and not comparable across separate queries, search strings, filter contexts or index rebuilds. So rank with it inside one query, and never store the number or show it to users as a confidence figure. Microsoft's announcement talks about semantic similarity, but the function reference describes word-based scoring. Do not expect it to behave like vector search over embeddings or to know that two different words mean the same thing. It scores on words, with stemming and optional fuzzy matching on top.

Setting Up the Full-Text Index

Both functions need a persisted full-text index on the column, and they only work on Import, Dual and Direct Lake tables. Other storage modes return an error. The September summary sets out the prerequisites:

The model must be at compatibility level 1708
During the preview, indexing is configured through TMDL view on the web or XMLA metadata, by setting the column's full-text indexing behaviour. Desktop support and a proper UI are planned, not here yet
Microsoft recommends Full mode during the preview

Two cautions. The functions are evaluated row by row, and Microsoft tells you to test performance before using them in a calculated column over a very large table. And an index costs refresh time and model size, so index the two or three columns people actually search, not every text field. If you have not used the browser modelling tools yet, our guide to building semantic models without Power BI Desktop covers TMDL view on the web.

String Indexing for the functions you already use

String Indexing is the less flashy sibling. It does not add functions; it speeds up existing text operations such as SEARCH, CONTAINSSTRING and text slicers on Import and Direct Lake models. It needs compatibility level 1707, is set the same way, and has two modes: Auto builds the index in memory on first query and does not persist it, while Full builds and stores it at refresh. Microsoft suggests Auto for smaller models. Note that its post describes both indexing features as "coming soon" under a "Preview" heading, so check your tenant.

APPROXIMATEDISTINCTCOUNT on Import and Direct Lake

Distinct counts are among the most expensive things you can ask the engine to do. APPROXIMATEDISTINCTCOUNT has existed for some DirectQuery sources for a while; it now works on Import, Direct Lake on OneLake and Direct Lake on SQL (without fallback), in preview, using an engine-side HyperLogLog algorithm.

Microsoft's numbers are the guide: an error rate of about 1.6% on Import and Direct Lake models. Its guidance is equally clear about where not to use it. On low-cardinality columns it can be slower and use more memory than DISTINCTCOUNT. So the rule we apply:

Use it for millions of distinct values - session IDs, transaction IDs, visitor counts - on trend visuals where 1.6% does not change a decision
Do not use it for country, category or status, for anything finance reconciles, or for numbers that appear next to an exact count elsewhere

If a slow distinct count is your bottleneck, also check the model first. Our guide to why Power BI reports are slow covers the usual culprits.

Calculated Columns for Direct Lake

The most requested Direct Lake gap is now closed, in preview: calculated columns on Direct Lake on OneLake models, authored in web modelling or Desktop. They work differently from Import calculated columns:

They use a new Expression Context property, and Direct Lake supports only User Context
They are evaluated at query time and not stored, so they cannot be used as relationship keys
They respect row-level and object-level security, because they are evaluated as the user
Direct Lake on SQL does not support them

The security point is the interesting one. Microsoft's example: a column derived from a field hidden by object-level security. In Import with the Standard context, a restricted user still sees the derived value, which quietly leaks the hidden field. With User Context, the derived column is unavailable to them too. If you rely on object-level security alongside RLS, that matters.

Our advice has not changed, though: push derived columns upstream into the Delta table where you can. Query-time columns cost query time. Use these for what only the model can do - translations with USERCULTURE, per-user values - and see our Direct Lake, Import and DirectQuery guide for where Direct Lake fits.

Where Solv Systems Comes In

None of this is production-ready yet, which is exactly when to test it on a copy of a real model. Our Power BI consulting team can benchmark these features against your data and tell you which ones will pay off when they reach general availability.

Sources and Further Reading

Frequently asked

It returns TRUE when text in a column matches a search string using full-text search rules rather than a simple substring match. It is case-insensitive, applies stemming and stop-word removal based on the model culture, and supports three match modes: TEXTMATCHING (any term, the default), FUZZYMATCHING (typo-tolerant) and PHRASEMATCHING (terms next to each other in order).

TEXTSIMILARITY returns a relevance score instead of TRUE or FALSE, so you can rank rows by how well they match. Microsoft describes it as an opaque lexical score: higher means a stronger match within one evaluation, 0 means no match, and scores are not comparable across separate queries.

A persisted full-text index on the column, which requires a semantic model at compatibility level 1708. During the preview you configure indexing through TMDL view on the web or XMLA metadata. The functions work on Import, Dual and Direct Lake tables; other storage modes return an error.

Only for high-cardinality columns where an estimate is acceptable. Microsoft gives an error rate of about 1.6% for Import and Direct Lake models, and warns that on low-cardinality columns it can be slower and use more memory than DISTINCTCOUNT. Keep exact counts for finance and anything reconciled.

Yes, in preview, for Direct Lake on OneLake models. They support only the User Context expression context, are evaluated at query time rather than stored, respect row-level and object-level security, and cannot be used in relationships. Direct Lake on SQL does not support them.

No. APPROXIMATEDISTINCTCOUNT on Import and Direct Lake, Direct Lake calculated columns, String Indexing and full-text indexing are all preview features. Microsoft's September 2026 post also describes the two indexing features as coming soon, so check they have reached your tenant before planning around them.