Powerbi Performance Scale

Skill: Performance & Scale — Enterprise-Grade Power BI

Trigger

Activate when the user mentions: slow report, performance, DirectQuery, DirectLake, Import mode, aggregations, composite model, large dataset, timeout, query performance, DAX Studio, Performance Analyzer, V-Order, OPTIMIZE, memory, Premium, Fabric capacity, large model

What You Know

You have optimised Power BI semantic models from sub-optimal 10-second page loads down to sub-second. You understand the full query path from DAX → xmVelocity → Formula Engine → Storage Engine → source data, and where to intervene at each layer.

Storage Mode Decision Framework

Data volume & refresh needs → Choose mode:

< 1GB model, needs latest data always?  → Import Mode (default, fastest for users)
> 1GB, PII/live data required?          → DirectLake (Fabric only, best of both)
Operational live data, < 1M rows?       → DirectQuery (last resort)
Mixed: history in Import + live in DQ?  → Composite Model
> 10GB, Premium/Fabric?                 → Large Dataset Storage + Import

Import Mode Optimisation

1. Remove unused columns — every column consumes memory
2. Remove unused tables — check with DAX Studio dependencies
3. Reduce cardinality — high-cardinality text columns kill compression
4. Correct data types — INT not TEXT for numeric IDs, DATE not DATETIME when time not needed
5. Disable auto date/time — creates hidden tables for every date column (massive size increase)

# Analyse model size and column cardinality
pbi-agent model analyse-size --port 12345
pbi-agent model unused-columns --threshold 0

DirectLake Mode (Fabric Only)

DirectLake reads directly from OneLake Delta tables — no import, no DirectQuery latency.

Requirements for optimal DirectLake:

✅ Delta tables with V-Order enabled (OPTIMIZE ... VORDER)
✅ Parquet file sizes 128MB–1GB
✅ Row group sizes ~1M rows
✅ Fabric capacity F64 or above for large models
✅ No unsupported DAX functions (check compatibility list)

Fallback to DirectQuery — triggers when:

• Query hits a non-Delta source
• Delta table is mid-update (transaction in progress)
• Model uses functions not supported in DirectLake

# Check if your Delta tables are DirectLake-ready
pbi-agent fabric validate-delta --lakehouse "Analytics" --table "fact_sales"

# Check V-Order status and optimize
pbi-agent fabric optimize-delta --table "fact_sales" --vorder

DAX Performance Patterns

Profile Before Optimising

Always measure first:

1. Performance Analyzer (Power BI Desktop) — shows per-visual query times
2. DAX Studio — copy the slow query, run Server Timings tab
3. Look at: FE (Formula Engine) vs SE (Storage Engine) ratio
   • High FE time → rewrite DAX logic
   • High SE time → data model structure issue (cardinality, relationships)

Common Slow DAX Patterns & Fixes

-- ❌ SLOW: FILTER on large fact table row-by-row
Slow = CALCULATE([Total Sales], FILTER(Sales, Sales[Amount] > 1000))

-- ✅ FAST: Use KEEPFILTERS for columnar filter
Fast = CALCULATE([Total Sales], KEEPFILTERS(Sales[Amount] > 1000))

-- ❌ SLOW: Nested CALCULATE with ALL inside FILTER
Slow2 = CALCULATE([Sales], FILTER(ALL(Customer), [Customer Rank] <= 10))

-- ✅ FAST: Pre-filter with TOPN
Fast2 = CALCULATE([Sales], TOPN(10, ALL(Customer[Name]), [Sales]))

-- ❌ SLOW: Computing same sub-expression multiple times
Slow3 =
    IF([Total Sales] > 1000000,
       [Total Sales] * 0.1,
       [Total Sales] * 0.05)

-- ✅ FAST: Variable for shared sub-expression
Fast3 =
VAR sales = [Total Sales]
RETURN IF(sales > 1000000, sales * 0.1, sales * 0.05)

Aggregation Tables (Pre-Aggregation Pattern)

For fact tables > 50M rows, create aggregation tables:

-- Gold layer: agg_sales_monthly
CREATE TABLE agg_sales_monthly AS
SELECT
    date_key / 100 AS year_month,  -- YYYYMM
    product_key,
    geography_key,
    SUM(sales_amount)   AS total_sales,
    SUM(quantity)       AS total_qty,
    COUNT(*)            AS transaction_count
FROM fact_sales
GROUP BY date_key / 100, product_key, geography_key;

Then configure as an Aggregation in Power BI:

• Maps agg_sales_monthly[total_sales] → fact_sales[sales_amount] (SUM)
• Power BI automatically routes queries to the agg table when possible

Capacity Sizing Guide (Fabric)

Refresh Optimisation

Large fact tables: Incremental refresh (keep 3 years, refresh last 7 days)
Dimension tables: Full refresh (typically small, fast)
Aggregation tables: Refresh after fact table refresh completes
Hot cache: Pin frequently-queried pages via API to pre-warm cache

# Configure incremental refresh policy
pbi-agent model set-incremental-refresh \
  --table "fact_sales" \
  --rolling-window-years 3 \
  --incremental-days 7