Power BI Full-Text Search with DAX: TEXTCONTAINS and TEXTSIMILARITY
Introduction
Power BI’s September 2026 preview adds full-text search to semantic models through two DAX functions: TEXTCONTAINS for finding matching rows and TEXTSIMILARITY for ordering them by lexical relevance. The feature is easy to misunderstand: it needs a persisted column index, depends on model culture, and does not produce an AI meaning score. This guide walks through setup, a support-ticket example, and the checks that make results useful.
Why full-text search matters in Power BI
Many useful business questions are buried in text: Which service tickets mention a failing battery? What do customers say about delivery? Which product descriptions refer to a particular feature? In a report, a search box that depends on exact character sequences can miss everyday variations such as singular and plural forms, a misspelling, or a phrase written in a different order.
Full-text search treats a column as searchable language rather than a long string to scan for a literal substring. The new DAX functions can match words and phrases, use linguistic processing such as stemming and stop-word handling, and optionally tolerate some spelling variation. That gives analysts a new way to filter and rank text-heavy semantic models without first exporting the text to another search service.
This is a practical gap in the existing Power BI tutorial landscape: model design, DAX calculations, Power Query, and report performance are familiar subjects, while this preview feature introduces an additional model configuration step before the formulas work. The important workflow is index first, search second, then rank only when the question needs an ordered list.
What the new DAX functions do
TEXTCONTAINS evaluates a text column against a search expression and returns TRUE or FALSE. Use it when you need to filter a table to tickets that match a phrase or to test whether a row contains a relevant term. It is a matching function, not a ranking function; two rows that both match are simply both matches.
TEXTSIMILARITY returns a floating-point relevance score for a text value and a search expression. Use the score to sort candidate rows so the strongest lexical matches appear first. The score is not a percentage, probability, or universal scale. It is meaningful within the context of a search, and Microsoft cautions that scores should not be compared across different queries or changed model conditions.
Both functions require a persisted full-text index on the column being searched. Microsoft’s current documentation lists Import, Dual, and Direct Lake storage modes as supported; unsupported modes, including DirectQuery, return an error. The functions are in preview, so check the current Microsoft documentation and your model’s compatibility before using them in a production report.
Do not confuse full-text indexing with the separate string-indexing preview. The property names are similar, but they enable different behavior: fullTextIndexingBehavior supports TEXTCONTAINS and TEXTSIMILARITY; stringIndexingBehavior accelerates existing character-oriented functions such as CONTAINSSTRING and SEARCH. Configure the property that matches the DAX function you intend to call.
Step-by-step: configure a full-text index
1. Confirm the model is eligible
The September 2026 preview summary lists compatibility level 1708 as the minimum for full-text indexing. Confirm that the semantic model is at the required level and uses a supported storage mode before editing metadata. If the model is below the minimum, review the compatibility-level change with your model owner first; compatibility changes can affect other model features and should be tested in a copy.
The preview setup described by Microsoft uses TMDL in the Power BI Service or XMLA metadata. A dedicated setting in Power BI Desktop is planned, but it is not the documented setup path for this preview. The exact place to edit depends on the workspace, capacity, and permissions available to you.
2. Choose the column deliberately
Start with one text column that users will actually search, such as Feedback[Comment] or Tickets[Description]. Avoid enabling the index on every string column just because it is available. Persistent indexes add model storage and can affect refresh duration, so focus on fields that contain meaningful text and support a real report question.
3. Set the TMDL property
In the model’s TMDL definition, place the fullTextIndexingBehavior property on the target column and set its value to full. Keep the column’s existing data type and source mapping, and follow the normal TMDL workflow for your model. The short property line below is the key setting; the surrounding table and column syntax varies with the model.
fullTextIndexingBehavior: fullFull mode builds and persists the index during model refresh. That is the simpler preview choice for a model where searches need to work consistently after refresh. The alternative explicit mode requires a separate index refresh operation, so use it only if you already manage model refreshes through a controlled XMLA process.
4. Apply and refresh
Apply the metadata change using the supported TMDL or XMLA workflow, then refresh the model so the persisted index is built. Confirm that the refresh completes and that the property remains on the intended column. If the model’s storage mode or compatibility level is ineligible, the index or later function call can fail rather than silently falling back to a scan.
5. Test in a query before building a report
Use DAX Query View or your team’s usual query tool to test a few known examples first. Include a straightforward match, a variation in word form, a phrase, and a likely typo if fuzzy matching is part of the requirement. Compare the returned rows with a small hand-checked set so you know what the model culture and match mode are doing to your data.
Practical example: find product feedback
Assume a Feedback table contains TicketID, Product, Comment, and CreatedOn. A support lead wants to find reviews about headphones even when a customer misspells the product name. With the Comment column indexed, TEXTCONTAINS can filter the rows using fuzzy matching. The example below is a DAX query, not a measure; replace the table and column names with your own model names.
EVALUATE
FILTER (
'Feedback',
TEXTCONTAINS (
'Feedback'[Comment],
"headphone", FUZZYMATCHING
)
)If the search should require more than one concept, combine separate TEXTCONTAINS calls with a logical AND. For example, searching for both “battery” and “life” is a different question from searching for either word. Test the behavior on real comments and choose the expression that best reflects how people phrase the issue.
Next, rank matching rows by similarity to a more specific phrase, such as “battery life”. The query below keeps matching candidates, adds a score, and returns the ten highest-scoring rows. This makes the list easier to review, but the score should be treated as an ordering signal rather than a measure of customer satisfaction or certainty.
EVALUATE
VAR Candidates =
FILTER (
'Feedback',
TEXTCONTAINS (
'Feedback'[Comment],
"battery life", FUZZYMATCHING
)
)
RETURN
TOPN (
10,
ADDCOLUMNS (
Candidates,
"MatchScore",
TEXTSIMILARITY (
'Feedback'[Comment],
"battery life", FUZZYMATCHING
)
),
[MatchScore], DESC
)A useful review view includes the ticket ID, product, comment, and score side by side. Keep the original text visible: a high score helps prioritize a result, but people still need to read the comment and decide whether it actually answers the business question. This is especially important when a report is used to classify complaints or trigger follow-up work.
Choose the right matching mode
The functions support text matching, phrase matching, and fuzzy matching. Text matching is a good starting point for word-based searches. Phrase matching is useful when the sequence of words matters, such as a specific product name or standard incident phrase. Fuzzy matching can help with minor spelling differences, but it should be validated against the vocabulary and error patterns in your own data.
The model culture affects linguistic behavior such as stemming and stop-word removal. A search for a word’s base form may match related word forms, while very common words may be treated differently. Culture is a model-level choice, so the same search expression can behave differently when the model culture changes. Test representative English, German, or other language text separately rather than assuming one language’s results generalize.
Case is handled without sensitivity by these functions, but accent marks are not folded away. For example, a query using “cafe” should not be assumed to match “café”. If accent variation matters, normalize or prepare the source text as part of data preparation, then test the normalized column with the same search expressions.
Performance, limits, and common mistakes
First, index only what you plan to search. Each persisted index uses model resources, and full mode can increase refresh time and model size. Start with a small number of high-value columns, record refresh duration and model size before and after, and expand only when the added search experience justifies the cost.
Second, use TEXTCONTAINS to decide whether a row qualifies and TEXTSIMILARITY to order qualified rows. Calling the scoring function for every row in a large calculated column can be expensive because the functions are evaluated row by row. Prefer testing in a query, applying selective filters, and measuring the experience on a representative model before scaling it.
Third, do not interpret a similarity score as a percentage or set a universal threshold such as 0.8 without testing. The score is contextual, and its meaning can shift with the query, the filtered data, the storage mode, or index changes. If you need a threshold, validate it against labeled examples for that exact search scenario and revisit it when the model changes.
Finally, do not assume full-text search understands intent like a generative AI system. The functions provide linguistic and lexical matching behavior; they do not know that “late shipment” and “delivery took too long” express the same business problem unless the matching behavior brings the terms together. For conceptual discovery, combine this feature with business-defined synonyms, curated categories, or a separate semantic search system.
Final thoughts
Full-text search gives Power BI semantic models a useful new way to work with comments, descriptions, and other text columns. The reliable pattern is straightforward: confirm compatibility and storage mode, persist an index on the specific column, use TEXTCONTAINS to filter, and use TEXTSIMILARITY only when the result order matters.
Because this is a preview feature, verify setup and behavior against Microsoft’s current documentation before rolling it into a production model. Start with one field and one well-defined search question, validate the result set with users, and monitor model size and refresh time as you expand.
Suggested screenshots and references
The workflow illustration in this article is an original explanatory graphic, not a screenshot of Power BI. For a production walkthrough, add a real TMDL View capture from your own model after confirming the property and model version, plus a DAX Query View capture showing the returned rows and their similarity scores. Do not present a synthetic UI as an actual Power BI screen.
For verified feature status, setup details, function behavior, and the separate string-indexing preview, consult these Microsoft references:
Comments