Power BI Performance Guide

Your specialized agent for optimizing Power BI performance — covering DAX optimization, data model design, report rendering, and following Microsoft's official performance best practices.

When to Use This Agent

Choose Power BI Performance Guide when:

• Optimizing slow Power BI reports and dashboards
• Improving DAX query performance and measure calculations
• Designing efficient data models (star schema, relationships, cardinality)
• Troubleshooting Power BI refresh times and gateway performance
• Implementing best practices for large datasets and DirectQuery

Consider alternatives when:

• You need general data analytics — use a data analyst agent
• You need SQL database optimization — use a database specialist agent
• You need BI strategy (not Power BI specific) — use a BI architect agent

Quick Start

Copy
# .claude/agents/power-bi-performance.yml
name: Power BI Performance Guide
model: claude-sonnet
tools:
  - Read
  - Write
  - Edit
  - Bash
  - Glob
  - Grep
description: Power BI performance specialist for DAX optimization, data model design, and report rendering efficiency

Example invocation:

claude "Our Power BI report takes 30 seconds to load with 5M rows. Analyze the data model and DAX measures for performance improvements — we're using Import mode with several calculated columns"

Core Concepts

Performance Optimization Layers

Layer	Impact	Optimization
Data Model	Highest	Star schema, proper relationships, remove unused columns
DAX Measures	High	Efficient formulas, avoid CALCULATE nesting, use variables
Visuals	Medium	Reduce visual count, avoid custom visuals, limit interactions
Data Load	Medium	Incremental refresh, query folding, reduce row counts
Infrastructure	Low-Medium	Premium capacity, gateway tuning, dataset size

DAX Performance Patterns

Copy
// SLOW: Iterating with nested CALCULATE
Bad_Measure =
SUMX(
    Sales,
    CALCULATE(
        SUM(Returns[Amount]),
        FILTER(Returns, Returns[ProductID] = Sales[ProductID])
    )
)

// FAST: Using relationships and variables
Good_Measure =
VAR _TotalSales = SUM(Sales[Amount])
VAR _TotalReturns = SUM(Returns[Amount])
RETURN _TotalSales - _TotalReturns

Configuration

Parameter	Description	Default
connection_mode	Data connectivity (import, directquery, composite)	import
dataset_size	Approximate dataset size	provided per project
premium_capacity	Premium/Pro license available	false
gateway	On-premises data gateway in use	false
optimization_focus	Priority (load-time, interaction, refresh, all)	all

Best Practices

1. Design star schema data models. Flat wide tables and snowflake schemas kill Power BI performance. Create a proper star schema with fact tables (numeric, transactional) connected to dimension tables (descriptive, lookup). This is the single most impactful optimization.

2. Use variables (VAR/RETURN) in all complex DAX measures. Variables are evaluated once and reused, preventing redundant calculations. A measure that references the same sub-expression three times without variables calculates it three times. With VAR, it calculates once.

3. Remove columns you don't use in reports. Every column consumes memory in Import mode. Remove unused columns at the Power Query level, not with DAX. A table with 50 columns where you use 10 wastes 80% of its memory allocation.

4. Avoid calculated columns — use measures instead. Calculated columns are computed during refresh and stored in the model, consuming memory. Measures are computed at query time and don't consume storage. Use calculated columns only when you need the value in slicers or filters.

5. Limit the number of visuals per report page to under 10. Each visual generates a separate query. A page with 20 visuals fires 20 queries simultaneously, competing for resources. Group related information, use bookmarks for alternatives, and use drill-through for detail views.

Common Issues

Report loads fast with 100K rows but crawls at 5M rows. The data model and DAX may contain patterns that scale poorly (row-level iteration, high-cardinality columns in relationships). Switch from calculated columns to measures, reduce dimension cardinality, and verify query folding is occurring in Power Query.

DAX measure works correctly but is extremely slow. Complex CALCULATE/FILTER nesting, SUMX over large tables, and unnecessary context transitions cause slow DAX. Use DAX Studio's Server Timings to identify which measure consumes the most time, then refactor using variables and simpler patterns.

Scheduled refresh times out or runs too long. Large datasets with full refresh each time cause timeouts. Implement incremental refresh (only refresh recent data), optimize Power Query to fold queries back to the source, and ensure the data gateway has sufficient resources.