Performance Analyzer and DAX Query View

Quick Answer: Performance Analyzer (View tab) records how long each visual takes to load, broken into DAX query time, DirectQuery time, and visual rendering time. Clear the cache first for accurate results. DAX query view (separate view in Desktop) lets you write and test DAX queries directly, helping identify slow measures before they reach reports.

Performance Analyzer

Accessing Performance Analyzer

View tab → Performance Analyzer → Start Recording

What It Measures

For each visual on the page, Performance Analyzer records:

Best Practice: Accurate Measurement

1. Start recording from the Performance Analyzer pane
2. Clear the visual cache: right-click the Performance Analyzer area → Refresh Visuals (or press Ctrl+Shift+F5)
3. Interact with the report (change slicers, navigate pages)
4. Stop recording and analyze results

Why clear cache? Without clearing, Power BI may serve cached results, making visuals appear faster than they actually are for first-time users.

Interpreting Results

Copying the DAX Query

Click "Copy query" next to any visual to see the exact DAX sent to the engine. Paste it into DAX query view for further analysis and optimization.

DAX Query View

DAX query view is a dedicated view in Power BI Desktop for writing and testing DAX queries.

Accessing DAX Query View

Click the DAX query view icon in the left sidebar (looks like a code bracket), or:

View tab → DAX Query View

Writing DAX Queries

DAX queries use the EVALUATE keyword:

// Simple table evaluation
EVALUATE
Products

// Filtered evaluation
EVALUATE
FILTER(Products, Products[Category] = "Electronics")

// Measure testing with SUMMARIZE
EVALUATE
SUMMARIZECOLUMNS(
    Products[Category],
    "Total Revenue", SUM(Sales[Amount]),
    "Order Count", COUNTROWS(Sales)
)

// Testing a complex measure
DEFINE
    MEASURE Sales[Test Revenue YTD] = TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])
EVALUATE
SUMMARIZECOLUMNS(
    'Date'[Month Name],
    "YTD Revenue", [Test Revenue YTD]
)

Benefits of DAX Query View

• Test measures before adding them to the model
• Debug complex DAX by examining intermediate results
• Compare performance of different DAX approaches
• Explore data without creating visuals
• Validate calculations against expected results

Model Performance Optimization Strategies

1. Reduce Model Size

• Remove unnecessary columns — every column consumes memory
• Remove unnecessary rows — filter historical data to relevant periods
• Reduce cardinality — high-cardinality columns (many unique values) compress poorly
• Avoid calculated columns when Power Query can do the same transformation

2. Optimize Data Types

• Use Whole Number instead of Decimal when possible (better compression)
• Use Date instead of Date/Time if time component is not needed
• Avoid Text columns with high cardinality for filtering

3. Reduce Granularity

If your fact table has one row per second but reports only need daily aggregation:

• Aggregate in Power Query before loading
• Use Group By to summarize to the needed level
• Smaller tables = faster queries

4. Improve Relationship Design

• Ensure integer keys for relationships (faster than text keys)
• Use star schema — avoid snowflake or complex relationship chains
• Minimize bi-directional cross-filtering
• Set Assume Referential Integrity for DirectQuery relationships

5. Optimize DAX Measures

• Use variables to avoid redundant calculations
• Avoid iterators (SUMX) when simple aggregations (SUM) suffice
• Use DIVIDE() instead of IF checks for division
• Minimize use of FILTER() inside CALCULATE — prefer direct column filters
• Avoid DISTINCTCOUNT on high-cardinality columns when possible

On the Exam

The PL-300 frequently tests:

• Using Performance Analyzer to identify slow visuals
• Interpreting Performance Analyzer results (DAX time vs. visual display time)
• Writing DAX queries in DAX query view for testing
• Strategies for reducing model size and improving performance
• Understanding the impact of cardinality on compression and query speed