Skip to main content

Last updated by Errin O'Connor, Founder & Chief AI Architect, EPC Group

DAX performance problems come from 7 root causes — FE bottlenecks, SE bottlenecks, model design errors, query context complexity, time intelligence patterns, aggregation misconfiguration, capacity constraints. Six-tool methodology anchored by DAX Studio Server Timings + Tabular Editor 3 Best Practice Analyzer + VertiPaq Analyzer. Six-phase engagement: Baseline + Triage → Diagnosis → DAX Refactoring → Model Redesign → Capacity Tuning → Validation. Typical improvements: 5x-100x measure rewrites, 3x-10x model redesign, 20x-500x aggregations, 40-70% p95 improvement from capacity tuning.

Key Facts

  • 7 DAX slowness root causes — FE bottlenecks + SE bottlenecks + model errors + query context + time intelligence + aggregations + capacity
  • 6 tools: DAX Studio + Tabular Editor 3 + VertiPaq Analyzer + SQL Profiler + Fabric Capacity Metrics App + AMO/TOM libraries
  • 6-phase methodology from Baseline + Triage through Enablement: 4-8 weeks, fixed fee, scoped after discovery
  • 7 anti-patterns to fix first — calc cols for aggregations + FILTER inside CALCULATE misuse + DIVIDE without alt + SWITCH branches + SUMX(FILTER) + row-by-row + bidirectional shortcuts
  • 7 Fabric F-SKU capacity levers — right-sizing + workload management + dataset scale-out + incremental refresh + hybrid tables + aggregations + cache warming
  • Typical outcomes: 5x-100x measure improvements, 20x-500x aggregations, refresh 8-12hr → 30-90min, 90% dashboards under 2s response

A reproducible diagnosis sequence

A tuning result is only useful if someone else can repeat the measurement. Use the same sequence on every slow visual and record every run.

  1. Capture the visual’s query. Start recording in Performance Analyzer (the Optimize ribbon in Power BI Desktop, or View when editing a report in the service), refresh the visual and note the DAX query, Direct query, Visual display and Other durations. Copy query gives the exact DAX the visual sends; Run in DAX query view runs it in Power BI Desktop.
  2. Separate the query from the visual. If the DAX query time is small and Visual display or Other is large, the problem is the report page, such as too many visuals or a heavy custom visual, not the model.
  3. Time the query cold and warm. In DAX Studio, clear the cache, run the copied query with Server Timings on, and record total duration, formula engine time, storage engine time and the number of storage engine queries. Run it three times cold and three times warm and keep the medians.
  4. Classify the bottleneck. Mostly storage engine time points at the model and the data: cardinality, relationships or a slow DirectQuery source. Mostly formula engine time points at the DAX: iterators, large intermediate tables or repeated context transitions.
  5. Check the model size. VertiPaq Analyzer shows which tables and columns dominate memory; high-cardinality columns nobody filters or groups on are the usual first cut.
  6. Change one thing at a time, then re-run the same query with the same cache state, data volume and capacity load, and record before and after.

Write the conditions down with every result: rows in the fact table, Import or DirectQuery, cache state, capacity SKU and whether other workloads were running. A timing without its conditions cannot be repeated or compared. Download the DAX performance test log (CSV); its example rows are synthetic and labeled as such, not client data.

DAX Performance Tuning FAQ

What causes DAX performance problems in enterprise Power BI models?

Seven root causes of DAX slowness at enterprise scale: (1) Formula Engine bottlenecks — expensive iterators (SUMX + FILTER on large tables), row-by-row logic instead of set-based operations, materialization of large intermediate tables. (2) Storage Engine bottlenecks — insufficient VertiPaq compression from wide + high-cardinality columns (transaction IDs, GUIDs in fact tables), poor sort order, incorrect data types. (3) Model design errors — bidirectional relationships creating ambiguity + slower query plans, snowflake schema instead of star schema, calculated columns instead of measures for aggregations. (4) Query context complexity — deep filter contexts + complex CALCULATE modifiers + KEEPFILTERS interactions that FE can't optimize. (5) Time intelligence patterns — DATESYTD + DATEADD without proper date table + inactive relationships handled poorly. (6) User-defined aggregations misconfigured — user aggregations not applied to queries because of grouping mismatches. (7) Capacity constraint — Fabric F-SKU or Premium capacity undersized for concurrent user count + model size. Each root cause has a specific diagnostic pattern + fix.

What tools do you use for DAX performance tuning?

Six specialized tools + methodology: (1) DAX Studio — Server Timings capture (measures FE + SE + total time), Query Plan analyzer (logical + physical plan tree), storage engine query capture (VertiPaq scan patterns), Clear Cache before every measurement run. (2) Tabular Editor 3 — Best Practice Analyzer (200+ rules for model design + DAX patterns), model metadata inspection, C# scripting for bulk optimization, VertiPaq Analyzer integration. (3) VertiPaq Analyzer — column-by-column memory footprint, cardinality analysis, compression ratio, size-of-hierarchy. (4) SQL Server Profiler + Extended Events — capture DAX queries executed against Power BI Premium capacity or SSAS. (5) Power BI Premium Capacity Metrics App / Fabric Capacity Metrics App — capacity throttling patterns, background operation queueing, memory pressure. (6) Microsoft.AnalysisServices.Tabular AMO/TOM libraries — programmatic model inspection + optimization. Methodology: Server Timings-first isolation of FE vs SE bottleneck, then targeted DAX rewrite or model redesign, then re-measurement.

What is an EPC Group DAX performance engagement?

Six-phase methodology (typically 4-8 weeks, fixed fee quoted after discovery): (1) Phase 1 Baseline + Triage (1 week) — top 20 slowest queries captured via DAX Studio + Extended Events, semantic model inventory via VertiPaq Analyzer, Best Practice Analyzer scan, capacity utilization baseline. (2) Phase 2 Diagnosis (1-2 weeks) — Server Timings analysis per top query, root cause classification (FE vs SE vs model design vs capacity), fix strategy per root cause. (3) Phase 3 DAX Refactoring (1-2 weeks) — measure rewrites, calculated column → measure conversions, time intelligence pattern standardization, KEEPFILTERS + CROSSFILTER cleanup. (4) Phase 4 Model Redesign (1-2 weeks) — star schema conformance, bidirectional relationship elimination where possible, aggregations design, incremental refresh setup, hybrid tables where appropriate. (5) Phase 5 Capacity Tuning (1 week) — Fabric F-SKU or Premium capacity right-sizing, workload management, dataset scale-out, cache optimization. (6) Phase 6 Validation + Enablement — before/after query timings, developer training on DAX patterns + Best Practice Analyzer, ongoing monitoring runbook.

What DAX anti-patterns should enterprises fix first?

Seven high-frequency anti-patterns with proven fixes: (1) Calculated columns for aggregations — replace with measures using SUMX or explicit aggregations. (2) FILTER inside CALCULATE without KEEPFILTERS analysis — often produces wrong results + slow. Use table-level filters when semantically correct. (3) DIVIDE without alternate value handling — leaves users with blanks; add explicit alternate result. (4) SWITCH with many branches — use SWITCH(TRUE(),...) pattern + consider hierarchies + user aggregations. (5) SUMX(FILTER(fact, condition), fact[Amount]) — collapse to CALCULATE(SUM(fact[Amount]), condition) when semantically equivalent. (6) Row-by-row iteration for grand totals — use SUMMARIZECOLUMNS + CALCULATE patterns for set-based operations. (7) Bidirectional filters used for calculation shortcuts — usually indicates missing measure logic + creates ambiguity + slows queries. Fixes deliver 5x-100x query time improvements typical.

How do you tune Fabric F-SKU capacity for Power BI performance?

Seven Fabric F-SKU capacity tuning levers: (1) F-SKU right-sizing — Fabric Capacity Metrics App shows CU% + background ops queueing + interactive ops timing; upsize F-SKU when p95 CU% > 80% during business hours. (2) Workload management — separate capacities for reporting workloads vs data engineering workloads vs Copilot for Fabric workloads to prevent cross-workload contention. (3) Dataset scale-out — F-SKU + Premium supports dataset scale-out (read replicas) for high-concurrency reports; enable for datasets serving 100+ concurrent users. (4) Incremental refresh — configure per fact table with partition granularity + rolling window to reduce nightly refresh times from hours to minutes. (5) Hybrid tables — hot partition in DirectQuery, cold partitions in import mode. (6) Aggregations table — user + automatic aggregations for high-cardinality fact tables enable subsecond queries on billion-row fact tables. (7) Cache warming — Power Automate flow warms Analysis Services cache before business hours by pre-executing dashboard queries. EPC Group runbooks cover each lever.

What performance improvements are realistic to expect?

Six typical performance improvement bands from EPC Group engagements: (1) Slow DAX measure rewrites — 5x-100x query time improvement, often from 60+ seconds to under 3 seconds. (2) Model redesign to conforming star schema — 3x-10x improvement across all queries touching redesigned tables. (3) Aggregations table implementation — 20x-500x improvement on aggregate queries hitting billion-row fact tables. (4) Incremental refresh — nightly refresh reduced from 8-12 hours to 30-90 minutes. (5) Fabric F-SKU right-sizing + workload management — 40-70% reduction in p95 query time during peak business hours. (6) Bidirectional relationship elimination — often 30-60% improvement plus correctness fixes on measures that were producing wrong numbers. Combined engagement outcome: 90% of dashboards responding in under 2 seconds, capacity utilization 40-60% instead of 90%+, refresh windows meeting SLA. Report delivered to executive sponsors with before/after Vertipaq Analyzer + query timing evidence.

Who does EPC Group's enterprise DAX + Power BI tuning?

EPC Group's Power BI performance practice is anchored by Founder & Chief AI Architect Errin O'Connor and delivered by senior consultants with 15-20+ years continuous Microsoft data platform delivery. Credentials: (1) Microsoft Solutions Partner in Data & AI designation. (2) Errin O'Connor was a pre-release program participant for Power BI (codename Crescent) at Microsoft — foundational Power BI + SSAS Tabular experience predating the public product. (3) Author of Microsoft Press bestselling Power BI book — reference material for enterprise Power BI architects. (4) 1,500+ Power BI deployments. (5) Cross-vertical proof across healthcare (payer + provider revenue cycle), financial services (regulatory reporting), and government (federal analytics). (6) Named consultants with PL-300 (Power BI Data Analyst) + DP-500 (Enterprise Data Analyst) + DP-600 (Fabric Analytics Engineer) certifications. Delivered under fixed-fee scope with named senior lead + p95 query time SLA commitment.

Related EPC Group Services

DAX Performance Tuning Discovery Call

30-min qualification. Phase 1 Baseline + Triage kickoff within 5 business days. Call (888) 381-9725.

By submitting this form, you agree to our Privacy Policy. We respect your privacy and will never share your information.

Phone

(888) 381-9725

Email

contact@epcgroup.net

Address

4900 Woodway Drive, Suite 830, Houston, TX 77056

Business Hours

Monday-Friday, 8 AM - 7 PM CT

Quick Response Guarantee

We respond to all inquiries within one business day

AI assistant — not a human