Configure string indexing in Power BI semantic models (preview)

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

String indexing creates an optimized search structure for a string column. Supported DAX substring operations use the index instead of scanning a large string dictionary, which can improve query performance. String indexing doesn't change the result of an expression. If an index isn't available, the query uses the standard execution path.

This article shows you how to configure, refresh, and troubleshoot a string index in a Power BI semantic model.

Note

String indexing is a preview feature. Names, availability, and behavior might change before general availability.

String indexing can improve the performance of these DAX functions:

DAX function	Purpose	Index benefit
`CONTAINSSTRING`	Tests whether one text value contains another.	Accelerates substring matching.
`SEARCH`	Returns the position of a substring in text.	Accelerates substring locating.

Prerequisites

A Power BI semantic model that uses compatibility level 1707 or later. Upgrading a model's compatibility level is irreversible. If you are using TMDL view in the web, adding the property in TMDL will prompt you to upgrade the model's compatibility level automatically.
A column with the string data type in a table that uses Import, Dual, or Direct Lake storage mode. Pure DirectQuery tables aren't supported.
Read-write access to the semantic model metadata through TMDL, the Tabular Object Model (TOM), or TMSL over the XMLA endpoint.
A current version of your Power BI or Analysis Services client tool that recognizes compatibility level 1707, the stringIndexingBehavior property, and the indexes refresh type.
Permission to process or refresh the affected table.

The default auto behavior doesn't require a metadata change. Configure full or explicit only on columns that benefit from a persisted index.

Choose an indexing behavior

Configure string indexing independently for each string column. The stringIndexingBehavior property supports these values:

Behavior	Build behavior	Persistence	Recommended use
`auto` (default)	Builds lazily when a qualifying query first uses the column. An individual build attempt has a 25-second time limit. If the index can't be built within 25 seconds, it isn't created for that attempt.	Stays in memory while the column data is loaded. If the data is evicted and reloaded, the next qualifying query must rebuild the index.	Use when searches are infrequent, columns are small, or minimizing processing time and storage growth is more important than predictable first-query latency.
`full`	Builds during processing whenever the semantic model is refreshed.	Persists with the model and survives memory eviction.	Use for larger semantic models when you want complete indexing upfront and don't need frequent manual control. Recommended over `explicit` mode during preview.
`explicit`	Builds only when you run an `indexes` refresh.	Persists with the model and survives memory eviction.	Use when you need explicit control over when the index is updated. During preview, you must use the XMLA endpoint to build or update the index.
`off`	Doesn't build an index.	No index is maintained.	Use when the column shouldn't use string indexing.

Persisted indexes increase semantic model size and can add CPU, memory, and elapsed time to refresh operations. The impact depends on string cardinality, dictionary size, the number of indexed columns, and refresh parallelism. Enable persistence only for columns used in representative substring-search workloads.

Enable a persisted string index

Set stringIndexingBehavior to full or explicit on each eligible string column that you want to persistently index. You can configure the property in TMDL or TMSL.

Configure the index with TMDL

Use the TMDL view in the Power BI service to add the property to a string column.

Open the semantic model in the Power BI service, and then open TMDL view.
Select or drag the semantic model definition onto the TMDL canvas.
Add the stringIndexingBehavior property to the string column that you want to index. The following example configures the NAME_HOTEL column for indexing during model refresh:
```
table Hotel_Reviews
    column NAME_HOTEL
        dataType: string
        sourceColumn: NAME_HOTEL
        stringIndexingBehavior: full
```
Apply the TMDL changes to the semantic model.
Refresh the semantic model to build the index.

Configure the index with TMSL

Use a createOrReplace operation that includes the complete target column definition.

Connect to the semantic model through the XMLA endpoint with a tool that can run TMSL commands.

Run a createOrReplace command. The following example enables full indexing for the ProductName column:

{
    "createOrReplace": {
        "object": {
            "database": "SalesModel",
            "table": "Product",
            "column": "ProductName"
        },
        "column": {
            "name": "ProductName",
            "dataType": "string",
            "sourceColumn": "ProductName",
            "stringIndexingBehavior": "full"
        }
    }
}

Process or refresh the affected table to build the index.

Caution

A createOrReplace command replaces the addressed metadata object. Include all required existing column properties instead of submitting only stringIndexingBehavior.

Refresh an explicit index

For a column that uses explicit behavior, run an indexes refresh after you enable the property and whenever the underlying column data changes. During preview, the indexes refresh type is available only through XMLA and TMSL.

Complete and commit any refresh operation that changes data, including a dataOnly or full refresh. For a calculated column, complete and commit the calculate or recalculate operation.
In a separate transaction, run an indexes refresh. Combining a data-producing refresh and an indexes refresh in one transaction doesn't rebuild an explicit index.

The following example refreshes indexes at the database level:
```
{
    "refresh": {
        "type": "indexes",
        "objects": [
            {
                "database": "SalesSemanticModel"
            }
        ]
    }
}
```
Wait for the refresh operation to complete before running a query that uses the index.

Return a column to automatic indexing

Set stringIndexingBehavior to auto to stop persisting the index and return to query-time behavior:

table Product
    column ProductName
        stringIndexingBehavior: auto

Apply the metadata change, and then process the affected model as required by your deployment workflow.

Troubleshoot string indexing

Use the following guidance to resolve common indexing issues.

`StringIndexingBehavior` can be enabled only for string columns

The explicit or full behavior was assigned to a non-string column. Select a string column, or use off.

No persisted index is created in `explicit` mode

Setting the metadata doesn't build the index. Run an XMLA refresh with "type": "indexes".

An `explicit` index is unavailable after refresh

A dataOnly or full refresh invalidates the previous index, and an indexes refresh in the same transaction doesn't rebuild it. A calculated column must also be recalculated first.

Commit data processing first. For a calculated column, complete and commit recalculation. Then run an indexes refresh in a separate transaction.

An `indexes` refresh fails immediately

The XMLA endpoint might not be read-write, the caller might lack write permission, or the database name might be incorrect. Verify the capacity XMLA settings, workspace permissions, and semantic model name.

A refresh runs out of memory

String index creation is memory-intensive. Concurrent builds, high-cardinality Unicode columns, or insufficient capacity memory can increase peak usage.

Reduce refresh parallelism, build fewer indexes at a time, use explicit to control build timing, schedule builds away from other memory-intensive workloads, or increase capacity. Set columns to auto or off when the index doesn't provide enough benefit.

Query performance doesn't improve

The query might not be eligible, the column might be off, a Dual query might use DirectQuery, or a Direct Lake query might use a fallback plan. With auto, the first eligible query can also incur the build cost or exceed the build time limit.

Confirm the storage mode, query execution path, property value, and refresh result. Compare the first and subsequent runs of a representative query, and investigate index build failures or timeouts.

General troubleshooting resources

Limitations and considerations

Configuration and tooling

Configure stringIndexingBehavior by using TMDL in the Power BI service or through XMLA. A graphical property editor isn't currently available.
Refreshing an explicit index is available only through the XMLA endpoint. You can't initiate an indexes refresh from the standard Power BI refresh interface or Power BI REST refresh API.
There isn't currently a customer-facing interface that confirms whether a persisted index exists. Use refresh results, traces, and query-performance comparisons to verify the index.
Compatibility level 1707 or later is required to configure stringIndexingBehavior. Enhancements to the default auto behavior don't require a separate metadata setting.
Older client libraries and modeling tools might not recognize compatibility level 1707, the stringIndexingBehavior property, or the preview indexes refresh type. Use current Power BI and Analysis Services client tools.

Storage modes and query eligibility

Only columns with the string data type can use string index.
Pure DirectQuery tables aren't supported.
Dual acceleration applies only when the query uses locally materialized VertiPaq data. A Dual query that uses DirectQuery doesn't use the string index.
Direct Lake acceleration applies only to locally materialized columns in VertiPaq plans. Fallback, DirectQuery, and non-materialized columns don't use the string index.
The default auto behavior is nonpersistent and doesn't guarantee that an index is built for every column or query.
Not every string expression, Unicode pattern, or query shape can use a string index. Validate the performance benefit on representative workloads before you enable persistence broadly.

Refresh and processing

Import-only incremental refresh is supported. Incremental refresh with real-time DirectQuery isn't supported because it creates a hybrid table.
After incremental data changes, an explicit index requires a separately committed indexes refresh. A full index is maintained through its supported processing behavior.
For full, a dataOnly refresh builds affected data-column indexes, and calculate or recalculate builds calculated-column indexes. For explicit, a dataOnly or full refresh invalidates the previous index. Commit processing, and then run an indexes refresh separately.

Resource usage

Building string indexes is memory-intensive, particularly for high-cardinality or large Unicode string columns. Building several indexes concurrently can cause substantial capacity memory pressure or an out-of-memory failure.
Persisted indexes increase semantic model size and can add CPU, memory, and elapsed time to refresh operations.
Direct Lake auto-sync and indexing: Enabling persisted string indexing can increase the time required for auto-sync. As a result, data changes might become available in the semantic model later than expected; the delay depends on model size and the number and complexity of indexed columns. For more predictable refresh behavior during preview, consider using full indexing with auto-sync disabled and refresh the model on a controlled schedule. If you keep auto-sync enabled, monitor data freshness and allow additional time for indexing to complete.

Feedback

Was this page helpful?

Last updated on 2026-09-29