A Step-by-Step Guide to Improve Your Data Model Performance Using Best Practices in Power BI

Data model performance is the foundation of every fast, responsive Power BI report. A poorly designed model can turn a simple dashboard into a frustrating experience with 30-second load times and timeout errors. At EPC Group, we have optimized Power BI data models for Fortune 500 organizations handling billions of rows, and the principles we apply are consistent: reduce model size, simplify relationships, optimize DAX, and let the VertiPaq engine do what it does best.

Step 1: Implement a Star Schema Design

The single most impactful change you can make to your Power BI data model is restructuring it into a star schema. The VertiPaq columnar engine is specifically optimized for star schemas, where narrow fact tables connect to descriptive dimension tables via single-column relationships.

• Separate your data into fact tables (transactions, events, measurements) and dimension tables (products, dates, customers, geography)
• Remove all table-to-table relationships that create many-to-many paths without bridge tables
• Denormalize dimension tables so each dimension is a single flat table rather than a snowflake of normalized tables
• Create a dedicated Date dimension table using CALENDARAUTO() or a custom date table with fiscal year support
• Ensure every relationship is one-to-many from dimension to fact, with single-direction cross-filtering

Organizations that migrate from flat, wide tables to a proper star schema typically see 40-70% improvement in query response times and 30-50% reduction in model file size.

Step 2: Reduce Column Cardinality and Remove Unnecessary Data

VertiPaq compresses data at the column level, and compression efficiency is directly tied to cardinality (the number of unique values in a column). Lower cardinality means better compression, smaller model size, and faster queries.

• Remove columns you do not use in any visual, filter, or DAX measure. Every unused column consumes memory.
• Split DateTime columns into separate Date and Time columns to dramatically reduce cardinality
• Round decimal values to the precision you actually need (e.g., two decimal places instead of fifteen)
• Replace high-cardinality text columns with integer keys and corresponding dimension lookups
• Use Power Query to filter rows at import time: exclude historical data beyond your reporting window
• Disable Auto Date/Time in Power BI Desktop options to prevent hidden date tables from inflating your model

Use the VertiPaq Analyzer (available via DAX Studio or the external tool Bravo) to identify which columns are consuming the most memory. Focus optimization efforts on the top 10 largest columns first.

Step 3: Optimize DAX Measures and Calculations

Inefficient DAX is the most common cause of slow report rendering. The difference between a well-written and poorly-written DAX measure can be the difference between a 1-second and a 60-second query.

• Replace calculated columns with measures wherever possible. Measures compute on demand; calculated columns consume memory permanently.
• Avoid using FILTER(ALL(Table)) on large tables. Use CALCULATE with direct filter arguments instead.
• Use SUMX, AVERAGEX, and other iterators only when row-by-row calculation is genuinely required
• Replace nested IF statements with SWITCH(TRUE(), ...) for cleaner and sometimes faster evaluation
• Use variables (VAR / RETURN) to store intermediate results and avoid recalculating the same expression multiple times
• Prefer DISTINCTCOUNT over COUNTROWS(DISTINCT(...)) for better query plan generation
• Avoid EARLIER and EARLIEST functions; use variables or CALCULATETABLE instead

Use DAX Studio and Performance Analyzer in Power BI Desktop to profile slow measures. Look at the Storage Engine (SE) and Formula Engine (FE) timing breakdown to determine whether the bottleneck is data retrieval or calculation.

Step 4: Optimize Relationships and Cross-Filtering

Relationship configuration has a direct impact on query performance and model behavior. Misconfigured relationships can cause unexpected results and slow queries.

• Use single-direction cross-filtering for all relationships unless bidirectional is absolutely required for a specific visual
• Avoid bidirectional relationships on large tables as they expand the filter context and increase memory usage
• Set inactive relationships where you need alternate date paths and activate them with USERELATIONSHIP in specific measures
• Validate referential integrity when your source guarantees it, allowing the engine to use INNER JOINs instead of OUTER JOINs
• Eliminate circular dependency warnings by restructuring relationships or using disconnected tables

Step 5: Leverage Query Folding and Incremental Refresh

Query folding pushes data transformation logic back to the source database, which is almost always faster than processing data in the Power Query engine. Incremental refresh ensures you only reload new and changed data, not the entire dataset.

• Check query folding status by right-clicking steps in Power Query and looking for "View Native Query"
• Keep folding-compatible transformations (filters, column selection, joins) before non-foldable steps
• Avoid custom M functions, sorting, and type changes early in the query pipeline as they can break folding
• Configure incremental refresh with a rolling window (e.g., 3 years of history, refresh last 30 days)
• Combine incremental refresh with real-time data using DirectQuery partitions in Premium/Fabric capacities

Step 6: Test, Measure, and Monitor Continuously

Performance optimization is not a one-time exercise. As data volumes grow and new reports are added, models can degrade. Establish a continuous monitoring practice.

• Use Performance Analyzer in Power BI Desktop to capture query timing for every visual on a page
• Run DAX Studio queries against production models to benchmark specific measures
• Monitor dataset refresh duration trends in the Power BI Admin portal
• Set up Power Automate alerts when refresh durations exceed acceptable thresholds
• Conduct quarterly model health reviews using the Best Practice Analyzer rules in Tabular Editor
• Document your model design decisions and share them with the team via a model governance wiki

Frequently Asked Questions