Why Your Power BI File Is 2GB
and How to Shrink It

A model with forty million rows sits at 400 MB. Another with four million sits at 2 GB. The row count is not what determines size, and until you know what does, shrinking a model is guesswork.

What determines it is cardinality — the number of distinct values in each column — and a handful of specific mistakes that inflate it.

Size Is Cardinality, Not Row Count

Power BI stores data column by column and compresses each column independently, largely by replacing repeated values with references to a dictionary of distinct values.

A column holding forty distinct region names across forty million rows compresses to almost nothing: a forty-entry dictionary plus a compact list of references. A column holding forty million distinct order IDs cannot compress at all, because there is no repetition to exploit.

So one column can be larger than the rest of the model combined, and finding which one is the whole job. VertiPaq Analyzer, inside DAX Studio, shows you this directly — sort columns by size and read the top five.

1. Datetime Columns With Time Included

Usually the single largest object in a model, and almost always unintentional.

A datetime stored to the second on a table spanning three years has up to 94 million distinct values. Split into a date and a separate time column, the date has about 1,100 distinct values and the time has 86,400. Two small dictionaries instead of one enormous one.

The split, in Power Query

// Instead of one column:
OrderDateTime    2026-09-16 14:23:07
                 -> up to 94,000,000 distinct values

// Two columns:
OrderDate        2026-09-16     ~1,100 distinct
OrderTime        14:23:07       ~86,400 distinct

// If you do not need seconds, round to the minute
// or the hour and the time column shrinks further.

// If you do not need time at all, drop it. Most
// reporting does not, and nobody checks.

This one change alone has taken models from 2 GB to under 600 MB in my experience, and it changes nothing about what the reports can show.

2. Calculated Columns

Every calculated column is computed at refresh and stored permanently. Worse, calculated columns often compress less well than imported ones, because the engine has less opportunity to optimise their encoding.

The fix is the one from the performance article: if the value depends on what the user is looking at, it should be a measure. If it is descriptive, it belongs on a dimension. Very little genuinely needs to be a calculated column on a fact table.

In VertiPaq Analyzer the Type column tells you which columns are calculated. Any of those in your top five is a finding.

3. Free Text and High-Cardinality IDs

Order numbers, transaction GUIDs, free-text comments, concatenated keys. All are effectively unique per row, so none of them compress.

The question to ask of each: does any report actually use it? Order number is often kept because it feels important, while no visual references it and nobody drills to that level.

Where a unique key is genuinely needed for relationships, an integer surrogate key compresses far better than a text key. Replacing a 30-character alphanumeric key with an integer can shrink that column by an order of magnitude.

4. Columns Nobody Uses

The cheapest win on this list. Most models load whole tables because that was the default, and carry columns no report has ever referenced.

Every loaded column costs memory whether or not anything uses it. Removing them in Power Query is minutes of work and frequently gives back a quarter of the model.

5. A Flat Model

Covered in detail elsewhere, but it belongs on this list because it is a size problem as much as a speed one: descriptive attributes repeated on every row of a fact table are the same compression failure as high cardinality, applied at scale.

Splitting into dimensions typically halves a model or better, because each descriptive value is then stored once rather than once per transaction.

6. Auto Date/Time, Silently

Power BI creates a hidden date table for every date column in your model unless you turn the feature off. On a model with a dozen date columns that is a dozen invisible tables, each with a row per day across the full range and several generated attribute columns.

None of it appears in the field list, so people optimise around it for months without knowing it is there. It shows in VertiPaq Analyzer as tables with names beginning LocalDateTable.

Turn it off in options, build one proper date dimension, and relate your date columns to it. On a model with many date columns this alone can recover a substantial fraction of the size, and it fixes the time intelligence problems that come from having a dozen inconsistent date tables rather than one correct one.

Three Things That Do Not Help As Much As People Think

Deleting historical rows. Intuitive and usually disappointing. Halving your row count does not halve model size, because the dictionaries for your high-cardinality columns barely shrink — the distinct values are mostly still there. You lose analytical range for a modest saving.

Switching to DirectQuery to avoid storing data. It does remove the model size problem, and replaces it with a query performance problem on every user interaction plus load on your source. That is a trade, not a fix, and it is usually the wrong one when the actual issue is a badly shaped model.

Buying more capacity. It raises the ceiling without addressing why you are near it. A 2 GB model that should be 400 MB will be a 4 GB model that should be 800 MB in two years, and you will have paid for the privilege.

What to Do First

1. Run VertiPaq Analyzer and record the top five columns by size.
2. Split or drop any datetime column in that list. Usually the biggest single win, and the lowest risk.
3. Remove unused columns. Fast, safe, and often surprisingly large.
4. Convert calculated columns in the top five to measures or dimension attributes.
5. Replace text keys with integer surrogates where relationships depend on them.
6. Only then consider restructuring the model shape, which is the largest piece of work.
7. Re-measure after each step. The order above is deliberately cheapest-first.

When Size Is Genuinely Not the Problem

Not every large model is a broken one, and it is worth knowing when to stop optimising.

If your top columns by size are the fact table's measure columns and its integer keys — rather than datetimes, text or calculated columns — the model is already shaped correctly and the size reflects genuine data volume. At that point further reduction means removing data or accepting a different storage mode, both of which are business decisions rather than technical fixes.

Similarly, if the model is large but every report opens in under two seconds, size is a capacity planning question rather than a performance one. Optimising a model nobody is waiting on is effort spent for its own sake, and there is almost always something on the site with a worse return on attention.

The measurement that settles it: check query timings alongside model size. Slow and large means fix it. Fast and large means plan capacity and move on.

What We Would Do

This sequence is part of a performance audit. We record the baseline, work through the list, and report the size and query timings after each change so you can see which ones paid.

Size reduction is rarely the goal in itself — it is a proxy. A smaller model scans less, which is why the query timings improve alongside it.