Study guide
6.2 Model Size Reduction and Cardinality Management
Microsoft Power BI Data Analyst PL-300
Track your progress as you read
Finished sections get a check mark.
1 Introduction
About the PL-300 Exam The Power BI Ecosystem
2 Chapter 1: Get or Connect to Data
1.1 Data Source Types and Connections 1.2 Storage Modes: Import, DirectQuery, and Direct Lake 1.3 Data Source Settings and Parameters
3 Chapter 2: Profile and Clean the Data
2.1 Data Profiling in Power Query 2.2 Resolving Errors, Nulls, and Data Quality Issues 2.3 Data Types and Type Detection
4 Chapter 3: Transform and Load the Data
3.1 Column Transformations and Data Types 3.2 Grouping, Aggregating, Pivoting, and Unpivoting 3.3 Merge Queries, Append Queries, and Relationship Keys 3.4 Query Folding and Load Performance
5 Chapter 4: Design and Implement a Data Model
4.1 Star Schema Design 4.2 Relationships, Cardinality, and Cross-Filter Direction 4.3 Table and Column Properties 4.4 Calculated Columns and Calculated Tables
6 Chapter 5: Create Model Calculations with DAX
5.1 DAX Fundamentals and Measures 5.2 The CALCULATE Function 5.3 Time Intelligence Measures 5.4 Statistical Functions, Semi-Additive Measures, and Calculation Groups 5.5 Essential DAX Patterns and Functions Reference
7 Chapter 6: Optimize Model Performance
6.1 Performance Analyzer and DAX Query View 6.2 Model Size Reduction and Cardinality Management 6.3 DAX Optimization Patterns
8 Chapter 7: Create Reports
7.1 Visual Types and Selection 7.2 Visual Formatting and Conditional Formatting 7.3 Copilot in Power BI Reports 7.4 Report Page Configuration and Slicers
9 Chapter 8: Enhance Reports for Usability and Storytelling
8.1 Bookmarks, Navigation, and Drillthrough 8.2 Sorting, Selection Pane, Mobile Layout, and Accessibility 8.3 Export Settings and Automatic Page Refresh
10 Chapter 9: Identify Patterns and Trends
9.1 The Analyze Feature and AI Visuals 9.2 Using Copilot to Summarize the Semantic Model 9.3 Advanced Analytics Patterns
11 Chapter 10: Create and Manage Workspaces and Assets
10.1 Workspaces, Apps, and Content Distribution 10.2 Subscriptions, Alerts, and Semantic Model Refresh 10.3 Dashboards and Advanced Distribution
12 Chapter 11: Secure and Govern Power BI Items
11.1 Row-Level Security (RLS) 11.2 Sensitivity Labels, Item Access, and Governance 11.3 Deployment Pipelines and Content Lifecycle
6.2 Model Size Reduction and Cardinality Management
Key Takeaways Explain This Topic AI
Cardinality (distinct values per column) is the biggest driver of VertiPaq size; high-cardinality columns compress poorly.
Remove unused columns before loading, every column has overhead even if no visual references it.
Reduce rows by filtering history, aggregating to the needed granularity, or using incremental refresh and aggregation tables.
Integer keys compress better than text keys; create surrogate integer keys in Power Query when possible.
Disable Auto Date/Time so Power BI does not create a hidden date table per date column.
Last updated: June 2026
Quick Answer: Model size is driven by column count, row count, and cardinality (distinct values per column). Remove unused columns, filter unnecessary rows, use integers instead of text for keys, reduce decimal precision, and disable Auto Date/Time. High-cardinality text columns (GUIDs, free text, timestamps) are the worst offenders.
How VertiPaq Stores Data
Power BI's VertiPaq engine is a columnar store that compresses each column independently using dictionary encoding (mapping distinct values to small integer IDs), run-length encoding (collapsing repeated runs), and value encoding for integers. Compression quality therefore depends far more on how many distinct values a column has than on the row count alone.
- Factor Impact
- Number of columns Each column has overhead regardless of rows
- Number of rows More rows, more data to store
- Cardinality More distinct values, worse compression
- Data type Text > Decimal > Integer > Boolean (most to least)
- Value distribution Skewed data compresses better than uniform
- Cardinality Levels
- Level Example Compression
- Very low (2-10) Gender, Status, Boolean Excellent
- Low (10-1,000) Category, Region Very good
- Medium (1k-100k) City, Product Name Good
- High (100k+) Customer Email, Transaction ID Poor
- Very high (millions) GUID, free text, timestamps Very poor
Column Optimization
Every column in the model consumes memory, so remove any not used in visuals, measures, relationships, or row-level security. In Power Query, Remove Other Columns (select the ones you need, then remove others) is more future-proof than removing columns individually, because new source columns are excluded automatically.
Reduce precision where you can: use Date instead of Date/Time when time is irrelevant (a huge cardinality saving, since a datetime to the second has up to 86,400 distinct values per day), round decimals to the needed places, and split a high-cardinality datetime into separate low-cardinality Date and Time columns if both are needed.
Replace high-cardinality text keys with integer surrogate keys . A key like "PROD-ABC-12345" compresses far worse than 1, 2, 3. Create one in Power Query with Add Column > Index Column > From 1 , or with a merge against a key table.
Row Optimization
Filter history you do not need: Table.SelectRows(Source, each [Date] >= #date(2024, 1, 1)) . Aggregate to the reporting grain: if reports never drill below day but data is per-second, Group By Date and Product to sum Amount, which can cut millions of rows to thousands. For very large models, build aggregation tables , Power BI routes summary-level queries to the small aggregation and drillthrough queries to the detail, and use incremental refresh so only recent partitions reload.
Disable Auto Date/Time
File > Options > Data Load > Time Intelligence > uncheck "Auto date/time for new files" (and uncheck it for the current file under the current-file options).
Auto Date/Time silently creates a hidden date table for every date column in the model, each with year/quarter/month/day hierarchies. Ten date columns means ten hidden tables, a meaningful and invisible source of bloat. Replace it with one explicit Date dimension used for all date-based analysis and time intelligence.
Measuring and Diagnosing Size
Use external tools to find the offenders: DAX Studio's VertiPaq Analyzer reports per-column size, cardinality, and dictionary size, letting you target the few columns that dominate the model. Typically a small number of high-cardinality columns account for most of the file size.
On the Exam
The PL-300 tests identifying high-cardinality columns as size problems, choosing strategies to shrink the model, the effect of data types on compression, when to aggregate before loading, and disabling Auto Date/Time in favor of an explicit date table.
Size Is About Cardinality First
The most important mental shift for this objective is that distinct values, not row counts, drive model size . VertiPaq stores each column as a dictionary of unique values plus compact pointers, so a billion-row Boolean column compresses to almost nothing while a million-row GUID column barely compresses at all. This is why the fastest path to a smaller model is hunting down a handful of high-cardinality columns, free-text notes, GUIDs, email addresses, exact timestamps, and either removing them, splitting them, or lowering their precision.
Tools like DAX Studio's VertiPaq Analyzer rank columns by size so you spend effort where it pays off.
Practical Levers, in Priority Order
Work the levers roughly in this order. Remove columns you do not need in any visual, measure, relationship, or security rule, this is the highest-leverage, lowest-risk change. Lower precision : convert Date/Time to Date when time is irrelevant (a massive cardinality drop), round decimals, and replace verbose text keys with integer surrogate keys . Reduce rows by filtering history to the reporting window and aggregating to the needed grain in Power Query. Add aggregation tables and incremental refresh for very large fact tables so only summaries live in memory and only recent partitions reload.
Disable Auto Date/Time so the model is not carrying a hidden date table per date column.
The DirectQuery Trade-Off
When a fact table is simply too large to import, DirectQuery keeps detail at the source and queries it live, trading in-memory speed for currency and unlimited volume. Composite models combine the two, importing small, fast-changing or shared dimensions (set to Dual ) while leaving the huge fact in DirectQuery, and layering user-defined aggregations so common summary queries hit a tiny in-memory table. The exam expects you to recognize when import optimization is enough versus when DirectQuery or a composite model is the appropriate architecture for the data volume described.