Power BI Master Class – Advanced Performance Optimization

• Diagnose the performance of a Power BI model and report using native and external tools
• Design a data model based on star schema principles and on efficient data compression
• Write DAX that does not overload the Formula Engine – use variables, avoid unnecessary context transition and choose between iterators and native aggregations
• Implement calculation groups and optimized time intelligence
• Build efficient Power Query queries and write error-resilient code
• Choose the right architecture for your data volume and data freshness requirements – Import, DirectQuery, Composite or Direct Lake, including incremental refresh and aggregations
• Optimize reports at the visualization level
• Establish governance across models – capacity monitoring, CI/CD, documentation and bulk edits via TMDL

• The course is designed primarily for experienced Power BI developers and data analysts who already build production models and reports but are running into performance limits – slow or failing refreshes, slow DAX, or complex models with dozens of tables.
• It is equally suitable for data architects and BI team leads who want to establish consistent standards and governance across multiple models. The course does not start from the fundamentals of Power BI, DAX or Power Query – it builds on them and goes into depth.

• Independent work with Power BI Desktop on production projects
• Solid knowledge of DAX (CALCULATE, filter context, basic iterators)
• Knowledge of Power Query / the M language – transformations, parameters
• Understanding of data modeling principles (star schema, relationships, cardinality)
• Previous experience with Tabular Editor or DAX Studio is an advantage, but not a requirement

1 Performance diagnostics and measurement

• Why diagnostics must precede optimization; common mistakes – optimizing blindly, tuning the wrong layer of the problem
• Performance Analyzer – breaking down visual render time
• DAX Studio – server timings, Formula Engine vs. Storage Engine
• VertiPaq Analyzer – table size, cardinality, compression
• Best Practice Analyzer and Measure Killer
• Differences between measuring in Power BI Desktop and in the Service – hardware, cache, concurrency, gateway
• Hands-on exercise – diagnosing a model and identifying its main problems

2 Data model optimization

• Star schema vs. snowflake vs. flat table
• Dimension types – conformed, role-playing, degenerate, junk, SCD type 1/2/3
• Cardinality and compression in VertiPaq
• The move upstream principle (Roche's Maxim)
• Relationships and filtering – filter direction, bi-directional filtering, many-to-many and bridge tables
• Hands-on exercise – redesigning a model with introduced problems

3 DAX optimization

• Formula Engine vs. Storage Engine
• Variables (VAR) and code readability
• Context transition and its performance impact
• Iterators vs. native aggregations
• Time intelligence and calculation groups
• Hands-on exercise – fixing DAX anti-patterns using real trace data

4 Power Query and data loading

• Query evaluation and lazy evaluation
• Streaming vs. blocking operations, ordering of steps
• Query folding – how to verify it (View Native Query) and what breaks it
• Table.Buffer and List.Buffer
• Bulk operations and defensive error handling
• Hands-on exercise – repairing queries with broken folding

5 Architecture and capacity

• Import vs. DirectQuery vs. Composite
• Incremental refresh
• Hybrid tables and custom partitioning
• Aggregations
• Direct Lake (Microsoft Fabric)

6 Report and visualization optimization

• Filtering and Top N
• Number of visuals per page, drillthrough and tooltip pages
• RLS and its impact on cache
• Custom visuals and their performance impact

7 Governance and monitoring

• Refresh monitoring and alerting
• Capacity monitoring – Fabric Capacity Metrics app
• Standardization in CI/CD – Tabular Editor and Best Practice Analyzer in the deployment process
• Model documentation – descriptions, display folders
• TMDL – bulk model edits, editing in VS Code, Git version control

8 Final comprehensive lab

• End-to-end optimization of a model with problems spanning the full spectrum (model, DAX, Power Query, report)
• Presentation of the approach taken and the results achieved
• Summary of key principles and recommended resources for further self-study

Previous courses

POWER BI Master Class – DAX and visualisation [PBIMC_REP]

POWER BI Master Class - Power Query and integration [PBIMC_DA]