Power BI performance starts with VertiPaq

When a Power BI report is slow, the first instinct is to rewrite the DAX. More often the model is carrying columns nobody uses, timestamps stored to the second, and IDs that are unique on every row. Those are storage problems, and fixing them helps every query that touches the table, not just the one you were looking at.

This article explains how the VertiPaq engine stores an import model and what that means for design. The examples use a synthetic admissions table from the same invented hospital as the rest of this series: 176,986 spells from April 2023 to August 2026, generated by the code in projects/article-examples/power-bi/. The counts below are properties of that data (distinct values, runs of repeated values) computed by the script. They are not measurements of Power BI, and I haven’t invented any sizes or timings. VertiPaq Analyzer, covered below, is how you get real sizes for your own model.

How VertiPaq stores a table

One column at a time. An import model stores each column separately. A query that sums length of stay by specialty reads two columns, however many others the table has. That’s why wide tables are cheaper to query than you’d expect, and why each extra column still costs memory and refresh time even if no visual uses it.

Encoding. Each column gets one of two encodings:

• Value encoding stores the numbers themselves, shifted so they need fewer bits. Microsoft’s guidance is that numeric columns get this, and it’s the most efficient.
• Hash encoding, also called dictionary encoding, builds a dictionary of the column’s distinct values and stores, for each row, the integer position of its value in that dictionary. Text always uses it. The engine can also choose it for numbers.

Either way, what’s stored per row is an integer, packed into as few bits as its range needs.

Compression. On top of that, run-length encoding (RLE) stores a run of identical consecutive values once, with a count. RLE depends on row order, and VertiPaq chooses the order itself while processing. You don’t control it directly, but the data shows why low-cardinality columns compress so well:

In arrival order, specialty changes almost every row: 134,023 runs. Sorted by specialty, it’s seven runs. Admission date goes the other way, from one run per day (the rows arrive in date order) to 8,665. Every sort order helps some columns and hurts others. Columns with few distinct values are the ones that can collapse into a handful of runs.

What cardinality does

Cardinality is the number of distinct values in a column, and it drives almost everything. More distinct values means a bigger dictionary, more bits per row, and fewer repeated runs. Here are several columns of the same 176,986-row table:

The bits column is simple arithmetic, the base-2 logarithm of the distinct count rounded up. It shows the idea, not the engine’s exact layout. Two columns stand out: the spell ID and the admission timestamp are both unique, or nearly unique, on every row. They will almost certainly be among the largest columns in the table, because run-length encoding has nothing to work with when values never repeat.

Split date-time columns

Source systems often store the admission time to the second. Most reports need the date, and some need the hour. Storing the timestamp as one column gives you a dictionary that grows with every row you load. Splitting it into a date column and a time column rounded to the minute gives two small dictionaries: the date column gains one entry per day, and the time column can never hold more than 1,440 values.

Data table

Data loaded up to	Date-time to the second	Date + time to the minute
2023-04-01	3,920	1,132
2023-05-01	7,988	1,326
2023-06-01	11,873	1,439
2023-07-01	15,890	1,505
2023-08-01	19,859	1,553
2023-09-01	23,985	1,600
2023-10-01	28,263	1,641
2023-11-01	32,575	1,679
2023-12-01	37,009	1,712
2024-01-01	41,649	1,745
2024-02-01	46,077	1,774
2024-03-01	50,552	1,805
2024-04-01	54,840	1,835
2024-05-01	58,970	1,867
2024-06-01	62,937	1,897
2024-07-01	66,993	1,928
2024-08-01	71,007	1,959
2024-09-01	75,150	1,989
2024-10-01	79,675	2,020
2024-11-01	84,043	2,050
2024-12-01	88,763	2,081
2025-01-01	93,714	2,112
2025-02-01	98,091	2,140
2025-03-01	102,636	2,171
2025-04-01	107,022	2,201
2025-05-01	111,266	2,232
2025-06-01	115,268	2,262
2025-07-01	119,509	2,293
2025-08-01	123,685	2,324
2025-09-01	128,046	2,354
2025-10-01	132,709	2,385
2025-11-01	137,287	2,415
2025-12-01	142,319	2,446
2026-01-01	147,372	2,477
2026-02-01	151,832	2,505
2026-03-01	156,544	2,536
2026-04-01	160,995	2,566
2026-05-01	165,444	2,597
2026-06-01	169,700	2,627
2026-07-01	174,009	2,658
2026-08-01	176,739	2,678

Synthetic data. Source: projects/article-examples/power-bi.

By the August 2026 load, the single column holds 176,739 distinct values and the split pair 2,678 (1,238 dates and 1,440 minutes). The gap keeps widening as data arrives.

There’s a modelling reason too. A relationship matches values exactly, so a date-time of 14:37:12 on 3 March never matches the midnight value of 3 March in the Date table. You need a pure date column to relate to Date anyway. Do the split in Power Query or the source view, round the time to the precision anyone actually reports (minute, quarter hour, or just the hour), and drop the original.

Remove what nobody uses

Every column costs memory and refresh time whether or not a visual uses it. The candidates I look at first:

• Row-level IDs. The spell ID is unique on every row, which makes it one of the most expensive columns you can load. Keep it only if a report needs to show it (drill-through to a spell list) or count it. If you drop it, remember that the fact table may then contain identical rows, which matters for iterators; the CALCULATE article shows how.
• Text IDs with a prefix. Microsoft’s data reduction guidance suggests converting codes like SP123456 to integers where the prefix carries no information, so they can be value encoded.
• Descriptive columns on the fact table. Specialty names and site names belong in their dimensions, with a small integer key on the fact. The star schema article covers the design.
• History nobody reports on. Filtering the fact table to the years the reports use cuts every column at once.
• DAX calculated columns. They’re stored like other columns but typically compress less well, and they’re built after the tables load. Create them in Power Query or the source instead, unless they need DAX.
• Auto date/time. It adds a hidden date table for every date column. Turn it off once you have your own date table.

Formula engine and storage engine

A DAX query runs in two engines:

• The storage engine is VertiPaq itself. It scans compressed columns, applies simple filters, groups and aggregates, and it uses multiple threads. It caches the results of its queries.
• The formula engine does everything else: complex expressions, iterating over intermediate results, and putting the answer together. It runs on a single thread.

A fast query gets the storage engine to do most of the work in a few scans. A slow one usually makes the formula engine iterate over a large intermediate result, or makes the storage engine call back into the formula engine row by row. In DAX Studio’s storage engine query text, that callback shows up as CallbackDataID.

Some DAX habits push work the wrong way:

-- Slower: FILTER iterates the whole Admissions table to build a filter
Emergency Admissions :=
CALCULATE ( [Admissions], FILTER ( Admissions, Admissions[Admission Method] = "Emergency" ) )

-- Better: a column predicate the storage engine can apply directly
Emergency Admissions :=
CALCULATE ( [Admissions], KEEPFILTERS ( Admissions[Admission Method] = "Emergency" ) )

In this measure both return the same result (KEEPFILTERS keeps the intersection with existing filters that FILTER over the visible rows gives you), and Microsoft’s DAX guidance recommends the second form. The same applies to measure references inside iterators over fact tables, and to DISTINCTCOUNT on a near-unique column where a table at the right grain would let you use COUNTROWS.

Finding the problem

I work from the report inwards: find the slow visual, get its query, find where the time goes, then check the model.

1. Performance Analyzer. In Power BI Desktop, open the Optimize ribbon, select Performance analyzer, then Start recording and Refresh visuals. Each visual gets a breakdown:

• DAX query: time for the model to return results. If this dominates, the problem is the model or the DAX.
• Visual display: time to draw. If this dominates, the visual is usually plotting too many points, or it’s a heavy custom visual.
• Other: preparing queries and waiting for other visuals. A page with dozens of visuals queues most of them here.

Refresh twice and compare. The first run can include cold-cache effects, so don’t draw conclusions from one run. Copy query gives you the DAX the visual sent.

2. DAX Studio. Paste the query, turn on Server Timings, clear the cache, and run it. You get total duration split into formula engine and storage engine time, the number of storage engine queries, and how many were answered from cache. Clear the cache before every timed run; otherwise the second run is fast because of the cache, not your change. Many storage engine queries, or a large share of formula engine time, point at the DAX. A few long storage engine scans point at the model.

3. VertiPaq Analyzer. In DAX Studio, Advanced > View Metrics analyses the connected model and lists every table and column with its cardinality, total size, dictionary size, data size, hierarchy size and encoding. Sort columns by total size and work down from the top. You can export the result as a .vpax file (or an obfuscated .ovpax) to keep a before-and-after record or share it without the data.

In a model like this one, I’d expect the timestamp and the spell ID at the top. The only way to know is to look.

Checklist

Model:

• Every column has a reporting or modelling purpose; the rest are removed at source or in Power Query.
• Date-time columns are split into a date and a rounded time, or just a date.
• Row-level IDs are kept only when a report needs them.
• Text codes that are really numbers are converted to integers.
• Fact tables hold integer keys; names and labels live in dimensions.
• Calculated columns are replaced by Power Query or source columns where possible.
• Auto date/time is off.

DAX:

• Filters are column predicates, not FILTER over a whole table.
• Iterators over fact tables read columns, not measures.
• Distinct counts over near-unique columns are replaced by COUNTROWS at the right grain.

Process:

• Performance Analyzer identifies the slow visual before anyone rewrites anything.
• DAX Studio timings are taken with a cleared cache, before and after each change.
• A .vpax from VertiPaq Analyzer is saved before and after model changes.

For a worked semantic model, see the RTT waiting list semantic model project. The time intelligence article covers the date table, and DAX for waiting-list KPIs covers RTT measures.