Power BI Troubleshooting: Performance, DAX Errors and Gateway Failures

The Troubleshooting Approach: Diagnose Before Fixing

The wrong approach: "the dashboard is slow → add more Premium capacity." This costs $5,000/month to solve a problem caused by one poorly written DAX measure. The right approach: diagnose the root cause → apply the targeted fix. Every Power BI issue traces to one of: data model (relationships, cardinality, model size), DAX (measure logic, filter context, calculation efficiency), query (data source query performance, import vs. DirectQuery), infrastructure (capacity, gateway, network), or configuration (refresh settings, security rules, workspace permissions). Identify the category first — then diagnose within it.

Performance Issues: Slow Dashboards and Reports

Issue 1: Dashboard Takes 15-30 Seconds to Load

Diagnosis: Open Performance Analyzer in Power BI Desktop (View → Performance Analyzer → Start Recording → interact with visuals). Performance Analyzer shows: DAX query time per visual, render time, and other processing time. The slowest visual is your optimization target.

Common causes and fixes: A single visual queries 5 million rows → add aggregation tables (pre-computed summaries) and configure aggregations in the data model. A DAX measure uses SUMX over the entire table row-by-row → rewrite using CALCULATE + FILTER for set-based evaluation. Too many visuals on one page (15+ visuals each issuing a separate query) → reduce to 8-10 focused visuals, move secondary analysis to drill-through pages. Large images or custom visuals → optimize image sizes, evaluate whether the custom visual is necessary.

Issue 2: DirectQuery Reports Are Consistently Slow

Root cause: Every visual interaction sends a query to the source database. If the source database is slow, the report is slow. Fix: Evaluate switching to Import mode (data refreshed on schedule, queries answered from in-memory cache — 10-100x faster). If DirectQuery is required (real-time data necessity), optimize the source: create indexes on filtered/grouped columns, create materialized views for common query patterns, and reduce visual complexity (fewer visuals = fewer concurrent queries to the source). Consider dual storage mode: frequently-queried dimensions in Import, large fact tables in DirectQuery — balancing freshness with performance.

Issue 3: Report Loads Fast Initially, Slows After Interaction

Root cause: Slicer selections or cross-filtering create complex filter contexts that slow DAX evaluation. Diagnosis: Use DAX Studio to capture the query generated by the slow interaction. Examine the query plan for table scans or high-cardinality filters. Fix: Reduce slicer cardinality (a slicer with 50,000 distinct values creates query complexity — use a hierarchy or grouped slicer instead). Pre-aggregate at the grain the visual needs (don't query transaction-level detail for a monthly summary chart).

DAX Errors: Blank Results, Wrong Numbers, Circular Dependencies

Issue 4: DAX Measure Returns BLANK for Some Rows

Common causes: The measure references a column that doesn't exist in the current filter context (BLANK because no matching rows). The relationship between tables is inactive or incorrectly configured (BLANK because the filter doesn't propagate). Division by zero not handled (returns BLANK unless wrapped in IF or DIVIDE). Fix: Use DIVIDE(numerator, denominator, 0) instead of numerator/denominator (handles divide-by-zero). Verify relationships in Model view (active relationships shown as solid lines, inactive as dashed). Use ISBLANK() or COALESCE() to provide default values when the measure returns BLANK.

Issue 5: DAX Measure Shows Wrong Numbers

Diagnosis: The most common cause is filter context — the measure is evaluated in a filter context you didn't expect. Use DAX Studio's "Define Measure" to test the measure with explicit filter context. Check: are relationships filtering in the expected direction (single vs. bi-directional)? Is the measure respecting row context vs. filter context correctly (CALCULATE changes filter context; SUMX preserves row context)? Are security filters (RLS) affecting the calculation? Fix: Use REMOVEFILTERS() or ALL() to explicitly control which filters the measure ignores. Verify relationship direction in Model view. Add USERELATIONSHIP() if the measure should use an inactive relationship.

Issue 6: Circular Dependency Error

Root cause: Two or more calculated columns or measures reference each other, creating a loop. Most common pattern: Calculated Column A references Calculated Column B which references Column A. Fix: Break the circular reference by converting one calculated column to a measure (measures are evaluated lazily, breaking the dependency chain). Restructure the logic so that each calculation depends only on upstream (already-computed) values.

Refresh Failures: Timeouts, Memory, and Connection Errors

Issue 7: Refresh Timeout (Exceeds 2/5 Hour Limit)

Root cause: Dataset too large to refresh within the time limit. Pro: 2-hour limit. Premium: 5-hour limit (configurable to 24 hours). Fix: Implement incremental refresh (refresh only new/changed data — 90% time reduction for large datasets). Optimize the source query (add indexes, reduce joins, filter rows in the query instead of importing everything and filtering in Power BI). Reduce dataset size (remove unused columns, reduce date ranges, archive old data).

Issue 8: Refresh Memory Error

Root cause: Dataset exceeds the memory available on the capacity node during refresh (refresh requires approximately 2x the final dataset size — original + new data during processing). Fix: Reduce dataset size through optimization. Upgrade capacity (P1 → P2 doubles available memory). Implement table-level refresh (refresh only changed tables, not the entire dataset). Split large datasets into smaller semantic models.

Issue 9: Refresh Connection Error

Root cause: Gateway can't connect to the data source. Network issue, credential expired, source database down, or firewall blocking. Diagnosis: Check gateway logs (Event Viewer → Application → Data Management Gateway). Test connection from the gateway machine directly (can you connect to the SQL Server from the gateway machine using SSMS?). Fix: Re-enter credentials in the gateway data source settings. Verify network connectivity (firewall rules, VPN status). Check source database availability. If intermittent: implement gateway cluster for failover.

Gateway Issues: Disconnections and Timeouts

Issue 10: Gateway Disconnects Periodically

Common causes: Gateway machine running Windows Update and restarting (schedule updates outside refresh windows), memory exhaustion on the gateway machine (too many concurrent connections for the machine's RAM), or network instability between gateway and cloud service. Fix: Deploy gateway cluster (2+ machines — one handles traffic while the other restarts). Increase gateway machine RAM (16GB minimum for production, 32GB for heavy workloads). Monitor gateway machine performance (CPU, memory, network) continuously.

Issue 11: Gateway Queries Timeout

Root cause: The query sent to the on-premises source takes longer than the gateway timeout (default 300 seconds). Fix: Optimize the source query (indexes, query tuning on the source database). Increase gateway timeout in the gateway configuration file (Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config → HttpsConnectionTimeout). Consider moving the data source to the cloud (Azure SQL) — eliminating the gateway dependency entirely.

Capacity Issues: Throttling and Eviction

Issue 12: Premium Capacity Throttling

Symptoms: All reports slow simultaneously. Refresh queue backing up. Visual queries taking 3-5x longer than normal. Root cause: CPU utilization above 100% for sustained period — Power BI throttles (delays) all operations to prevent capacity failure. Diagnosis: Premium Capacity Metrics app → CPU chart → identify which workloads consume the most CPU. Fix: Optimize the top CPU-consuming workloads (usually one or two large datasets or complex DAX measures). If optimization isn't sufficient: scale the capacity (P1 → P2) or move some workloads to a separate capacity. Fabric F-SKUs with auto-scale handle burst workloads without permanent upsizing.

Issue 13: Dataset Eviction (Query Performance Degradation)

Symptoms: Reports that were fast become slow intermittently — especially after other reports are accessed. Root cause: Memory pressure — Power BI evicts datasets from memory to make room for other datasets. The next query to the evicted dataset requires reloading from storage (seconds instead of milliseconds). Fix: Reduce total dataset sizes in the capacity. Pin critical datasets in memory (Large Dataset Storage Format with increased memory allocation). Reduce dataset count per capacity. Upgrade capacity memory.

Data Model Issues: Relationships and Cardinality

Issue 14: Many-to-Many Relationship Producing Wrong Results

Root cause: Many-to-many (M:M) relationships between tables produce inflated numbers because the join multiplies rows. Fix: Introduce a bridge table (a distinct list of the join key) between the M:M tables. Use TREATAS() in DAX instead of physical relationships for complex M:M scenarios. Validate aggregation results against the source — M:M relationships can produce numbers that look plausible but are mathematically wrong.

Issue 15: High Cardinality Columns Bloating Model Size

Root cause: Columns with millions of unique values (transaction IDs, timestamps with seconds, long text descriptions) consume disproportionate memory. Fix: Remove columns not used in visuals or DAX (the transaction ID column that nobody filters or groups by — remove it). Reduce datetime granularity (do you need seconds or is daily sufficient?). Hash long text into shorter representations. These optimizations can reduce model size by 30-60% — dramatically improving performance and reducing capacity costs.

Diagnostic Tools: DAX Studio, Performance Analyzer, Tabular Editor

Go Deeper

Continue building your understanding with these related resources from our consulting practice.