In Power BI, indexing isn’t managed manually like in traditional databases, but it plays a crucial role in how data is stored, compressed, and retrieved efficiently — especially within the VertiPaq engine. Power BI uses a columnar storage structure, and behind the scenes, VertiPaq creates and maintains different types of indexes automatically to optimize query performance and memory usage.
There are mainly three types of indexing mechanisms in Power BI’s VertiPaq engine — dictionary encoding, run-length encoding, and hash indexing.
The first and most fundamental is dictionary encoding. VertiPaq stores unique values from each column in a separate structure called a dictionary and then replaces the actual column values with integer keys that point to entries in that dictionary. This drastically reduces memory usage because instead of storing repetitive text or numeric values, Power BI just stores small integer references. For example, if a “Region” column has only five distinct values across millions of rows, the dictionary will store those five unique regions, and the data itself will only contain integers 1–5. In one of my projects with customer demographic data, this mechanism alone reduced model size by around 70%.
Next is run-length encoding, which comes into play when columns have repeating values in consecutive rows, often seen in sorted columns like dates or status flags. VertiPaq doesn’t store each repeated value separately; instead, it stores the value once along with the count of how many times it appears consecutively. This is very effective when your tables are sorted properly before loading. I’ve used this to optimize performance by sorting my fact tables by date and key columns before import — the compression ratio improved, and query time dropped significantly.
The third type is hash indexing (or segment-level indexing), which VertiPaq uses internally to speed up data retrieval during queries. It divides data into segments, typically around 8 million rows each, and builds hash indexes that quickly locate which segment contains the requested value. For example, when Power BI executes a filter like Region = "East", it doesn’t scan the entire column; instead, it uses these hash indexes to locate the relevant segments, making the query extremely fast even on large datasets.
One of the challenges I faced early on was understanding why two models of similar size performed differently. After investigating with DAX Studio and VertiPaq Analyzer, I realized one table had a high-cardinality text column (like unique invoice numbers), which prevented effective dictionary compression. To fix it, I moved those columns to a separate lookup table and only kept numeric keys in the fact table — improving both compression and query speed.
A limitation of VertiPaq indexing is that it’s most efficient for read-heavy, analytical workloads, not for frequent updates. Because data is stored column-wise and compressed, each refresh or update requires re-encoding, which is why Power BI isn’t ideal for real-time transactional use cases.
As for alternatives, when dealing with real-time or near-real-time reporting, I switch to DirectQuery mode, where indexing is handled by the underlying database (like SQL Server). In that case, I manually tune clustered, non-clustered, or columnstore indexes in the source database for optimal performance. For hybrid scenarios, I use composite models that combine Import (VertiPaq indexing) for historical data and DirectQuery for live data.
So, while Power BI doesn’t expose manual index management, understanding how VertiPaq’s internal indexing — dictionary, run-length, and hash — works allows you to design models and data loads that maximize compression, minimize memory footprint, and deliver high-speed query performance.