Day 20: Power BI Performance Optimisation

Five most common causes: (1) Too many visuals on one page — each visual runs a DAX query; 30 visuals = 30 queries on page load. (2) Inefficient DAX — FILTER on large tables instead of CALCULATE with direct filter arguments. (3) Too many relationships or bidirectional relationships creating complex filter propagation. (4) DirectQuery with slow source database — every visual interaction sends a SQL query. (5) Large imported dataset with many columns and rows not needed for the report — import only required columns. Diagnose with Performance Analyser.

VertiPaq is Power BI's in-memory columnar storage engine that compresses and stores Import mode data. It uses column-level compression (values are stored as dictionary + encoded integers) which makes repeated values very cheap. Design implications: (1) High cardinality columns (unique ID per row) compress poorly — avoid importing unnecessary ID columns. (2) Text columns with many unique values take more memory than numeric columns. (3) Fewer columns = smaller model = faster queries. Use DAX Studio (free external tool) to analyse VertiPaq storage and identify large columns.

Six techniques: (1) Import only needed columns — remove every column not used in measures, visuals, or relationships. (2) Import only needed rows — filter source data in Power Query to the date range and scope needed. (3) Avoid calculated columns — use measures instead where possible; calculated columns are stored for every row. (4) Use integer keys instead of text keys for relationships — integers compress far better. (5) Replace high-cardinality text columns with coded values and a lookup table. (6) Remove date/time columns and replace with a separate Date table — the time component of DateTime columns explodes cardinality.

CALCULATE with direct filter argument: CALCULATE(SUM(Sales[Revenue]), Sales[Region] = "North") — efficient. Power BI optimises this directly against the column index. CALCULATE with FILTER: CALCULATE(SUM(Sales[Revenue]), FILTER(Sales, Sales[Region] = "North")) — slower. FILTER iterates every row of the Sales table and evaluates the condition for each row. For simple equality filters, always use CALCULATE with direct filter arguments. Use FILTER only when the condition is complex (row-level expression, multiple column references) and cannot be expressed as a direct argument.

Aggregations are pre-computed summary tables stored in the model that Power BI uses to answer high-level queries without scanning the full detail table. Example: a Sales fact table with 100M rows has a SalesAgg table summarised by Month and Product Category with total revenue. When a visual queries revenue by month, Power BI hits the small aggregation table (fast). When a visual drills to individual orders, it hits the full detail table. Configure via: right-click the aggregation table → Manage Aggregations → map each column to the detail table. This is a Power BI Premium feature that enables sub-second performance on very large datasets.