What's changed: Created PL-300 Chapter 2 (domain: Model the data): data model design (star schema, table/column properties, role-playing dimensions, cardinality/cross-filter direction, date table, calculated columns/tables), DAX calculations (measures vs calculated columns, CALCULATE, time intelligence, semi-additive, quick measures, calculation groups), and model performance optimization (remove unnecessary rows/columns, Performance Analyzer/DAX query view, reduce granularity).
2.3Optimize model performance
Understand optimizing the model by removing unnecessary rows/columns, identifying slow elements with Performance Analyzer and DAX query view, and reducing granularity.
A large, slow model hurts the experience. The optimization playbook is to trim the unnecessary, identify slow elements, and reduce granularity.
2.3.1Remove unnecessary rows/columns and granularity
Power BI Import (VertiPaq) compresses column-wise, so removing unnecessary columns most improves size and performance (especially high-cardinality columns—many distinct values). Remove unused rows too. Further, reducing granularity (e.g., line items to daily aggregates) sharply cuts row counts and lightens the model. Pre-aggregate to the granularity your analysis actually needs.
2.3.2Identifying slow elements
Performance Analyzer measures each visual’s DAX query time/visual display time on a report page to pinpoint slow visuals and measures. DAX query view lets you run/tune actual DAX queries to isolate causes. Use these to find slow measures/relationships/visuals and drive rewrites or model improvements.
Cues: "biggest impact on size/perf" = remove unnecessary (especially high-cardinality) columns. "fewer rows" = reduce granularity/pre-aggregate. "measure which visual/measure is slow" = Performance Analyzer. "run/tune DAX" = DAX query view.
Watch the mix-ups: (1) Removing columns beats removing rows for size (columnar compression). (2) High-cardinality columns hurt compression. (3) Performance Analyzer measures, it does not auto-fix. (4) Over-reducing granularity can break needed analysis.
2.3.3Section summary
- Columnar compression makes removing unnecessary (high-cardinality) columns most effective
- Reduce granularity/pre-aggregate to cut rows and lighten the model
- Use Performance Analyzer/DAX query view to find slow visuals/measures
Sign in to track progress — Log in.
Quick check
(just a quick review)Q1. What most effectively reduces an Import model’s size?
Q2. A huge line-item fact is needed only at daily aggregates. Best optimization?
Q3. You want to measure which visuals and measures are slow on a report page. Best tool?
Q4. You want to isolate a slow measure’s cause by running/tuning the actual DAX query. Best?
Q5. Which column trait most hinders VertiPaq compression and bloats the model?