ExamFinch.com My Study

Study guide

6.3 DAX Optimization Patterns

Practice this exam

Microsoft Power BI Data Analyst PL-300

Track your progress as you read

Finished sections get a check mark.

1 Introduction

About the PL-300 Exam The Power BI Ecosystem

2 Chapter 1: Get or Connect to Data

1.1 Data Source Types and Connections 1.2 Storage Modes: Import, DirectQuery, and Direct Lake 1.3 Data Source Settings and Parameters

3 Chapter 2: Profile and Clean the Data

2.1 Data Profiling in Power Query 2.2 Resolving Errors, Nulls, and Data Quality Issues 2.3 Data Types and Type Detection

4 Chapter 3: Transform and Load the Data

3.1 Column Transformations and Data Types 3.2 Grouping, Aggregating, Pivoting, and Unpivoting 3.3 Merge Queries, Append Queries, and Relationship Keys 3.4 Query Folding and Load Performance

5 Chapter 4: Design and Implement a Data Model

4.1 Star Schema Design 4.2 Relationships, Cardinality, and Cross-Filter Direction 4.3 Table and Column Properties 4.4 Calculated Columns and Calculated Tables

6 Chapter 5: Create Model Calculations with DAX

5.1 DAX Fundamentals and Measures 5.2 The CALCULATE Function 5.3 Time Intelligence Measures 5.4 Statistical Functions, Semi-Additive Measures, and Calculation Groups 5.5 Essential DAX Patterns and Functions Reference

7 Chapter 6: Optimize Model Performance

6.1 Performance Analyzer and DAX Query View 6.2 Model Size Reduction and Cardinality Management 6.3 DAX Optimization Patterns

8 Chapter 7: Create Reports

7.1 Visual Types and Selection 7.2 Visual Formatting and Conditional Formatting 7.3 Copilot in Power BI Reports 7.4 Report Page Configuration and Slicers

9 Chapter 8: Enhance Reports for Usability and Storytelling

8.1 Bookmarks, Navigation, and Drillthrough 8.2 Sorting, Selection Pane, Mobile Layout, and Accessibility 8.3 Export Settings and Automatic Page Refresh

10 Chapter 9: Identify Patterns and Trends

9.1 The Analyze Feature and AI Visuals 9.2 Using Copilot to Summarize the Semantic Model 9.3 Advanced Analytics Patterns

11 Chapter 10: Create and Manage Workspaces and Assets

10.1 Workspaces, Apps, and Content Distribution 10.2 Subscriptions, Alerts, and Semantic Model Refresh 10.3 Dashboards and Advanced Distribution

12 Chapter 11: Secure and Govern Power BI Items

11.1 Row-Level Security (RLS) 11.2 Sensitivity Labels, Item Access, and Governance 11.3 Deployment Pipelines and Content Lifecycle

6.3 DAX Optimization Patterns

Key Takeaways Explain This Topic AI

Use variables (VAR/RETURN) so a repeated expression is evaluated once instead of many times.

Prefer direct column filters in CALCULATE over FILTER(), direct filters run on the fast storage engine.

Use SUM rather than SUMX unless you genuinely need a per-row calculation before aggregating.

Replace IF(condition, SUM(), 0) patterns with CALCULATE(SUM(), condition) for cleaner, faster code.

Avoid DISTINCTCOUNT on very high-cardinality columns; APPROXIMATEDISTINCTCOUNT is faster but only works in DirectQuery.

Last updated: June 2026

Quick Answer: Use VAR to avoid redundant calculations. Prefer DIVIDE() over IF/division patterns. Use direct column filters in CALCULATE instead of FILTER(). Avoid DISTINCTCOUNT on million-value columns. Replace IF(condition, SUM(), 0) with CALCULATE(SUM(), condition). These changes can cut measure time substantially.

Storage Engine vs. Formula Engine

Understanding why a pattern is fast helps you optimize deliberately. Power BI splits work between two engines: the storage engine (VertiPaq/SE) , which is multi-threaded, cached, and very fast at scanning and aggregating columns, and the formula engine (FE) , which is single-threaded and handles logic the SE cannot. Optimizing DAX largely means keeping work in the storage engine and minimizing row-by-row formula-engine iteration. Direct column filters, SUM, and simple aggregations stay in the SE; FILTER over a table and complex iterators push work to the FE.

  • Variables Eliminate Redundancy // Before, SUM(Sales[Revenue]) evaluated twice
  • Profit Margin = DIVIDE(SUM(Sales[Revenue]) - SUM(Sales[Cost]), SUM(Sales[Revenue]))
  • // After, each aggregation evaluated once
  • Profit Margin =
  • VAR Revenue = SUM(Sales[Revenue])
  • VAR Cost = SUM(Sales[Cost])
  • RETURN DIVIDE(Revenue - Cost, Revenue)
  • Efficient Filtering in CALCULATE // Slow, FILTER iterates row by row (formula engine)
  • Premium Sales = CALCULATE(SUM(Sales[Amount]), FILTER(Products, Products[Price] > 100))
  • // Fast, direct column filter (storage engine)
  • Premium Sales = CALCULATE(SUM(Sales[Amount]), Products[Category] = "Premium")

Rule: Use FILTER() only when the condition must reference a measure or combine multiple columns. For simple column comparisons, use direct filters.

  • Avoid Unnecessary Iterators Total Revenue = SUMX(Sales, Sales[Amount]) // slow, needless iteration
  • Total Revenue = SUM(Sales[Amount]) // fast, pure storage engine
  • Reserve SUMX for genuine per-row math: Total Revenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
  • Replace IF Patterns with CALCULATE // Inefficient
  • Active Sales =
  • IF(HASONEVALUE(Products[Status]),
  • IF(VALUES(Products[Status]) = "Active", SUM(Sales[Amount]), 0),
  • SUM(Sales[Amount]))
  • // Efficient
  • Active Sales = CALCULATE(SUM(Sales[Amount]), Products[Status] = "Active")
  • SELECTEDVALUE over Complex Patterns // Verbose
  • Selected Region = CALCULATE(MAX(Regions[RegionName]), FILTER(Regions, COUNTROWS(Regions) = 1))
  • // Simple and efficient
  • Selected Region = SELECTEDVALUE(Regions[RegionName], "Multiple")

Distinct Counts on High-Cardinality Columns

DISTINCTCOUNT on a column with millions of distinct values is expensive. In DirectQuery models you can use APPROXIMATEDISTINCTCOUNT , which uses a HyperLogLog-style algorithm for a much faster approximate result. Note the important limitation: APPROXIMATEDISTINCTCOUNT works only in DirectQuery mode (against supported sources such as Azure SQL, Synapse, Snowflake, Databricks, and BigQuery), not in Import models. In Import mode, reduce the cost instead by pre-aggregating, lowering cardinality, or limiting the column's role.

Quick Optimization Checklist

  • Pattern Prefer Avoid
  • Repeated expression VAR reused Re-evaluating inline
  • Simple filter Column filter in CALCULATE FILTER(table, ...)
  • Column sum SUM SUMX over one column
  • Conditional sum CALCULATE(SUM(), cond) nested IF + SUM
  • Single value lookup SELECTEDVALUE CALCULATE + FILTER
  • Divide DIVIDE() / with IF guard
  • On the Exam

The PL-300 tests recognizing inefficient DAX and suggesting improvements, knowing when FILTER() is necessary vs. direct filters, using variables for readability and speed, choosing SUM vs. SUMX, and the storage-engine vs. formula-engine implications.

Optimize by Steering Work to the Storage Engine

The unifying principle behind every pattern here is the two-engine architecture. The storage engine is fast, cached, and parallel; the formula engine is single-threaded and handles whatever the storage engine cannot. Fast DAX maximizes storage-engine work (column scans, simple aggregations, boolean filters) and minimizes formula-engine iteration (row-by-row FILTER, complex iterators, repeated re-evaluation). When you replace a table FILTER with a column predicate, swap SUMX for SUM, or hoist a repeated expression into a VAR, you are moving work from the slow engine to the fast one or eliminating it entirely.

Naming the engines explicitly helps you predict which rewrite will actually help.

Worked Rewrite: Conditional Aggregation

Consider a measure meant to total only active products. A beginner writes nested IFs around HASONEVALUE and VALUES, forcing the formula engine to branch per context. The optimized form, CALCULATE(SUM(Sales[Amount]), Products[Status] = "Active") , pushes the whole job to the storage engine: the boolean predicate becomes a fast single-column filter and SUM aggregates in parallel. Same result, dramatically less work. This rewrite, IF-around-SUM into CALCULATE-with-predicate, is one of the most testable optimization patterns because it appears constantly in real models.

When the Bottleneck Is the Model, Not the Measure

Not every slow measure is a formula problem. If a measure must DISTINCTCOUNT a million-value column, or scan a fact table with poor compression, no amount of VAR tidying will save it, the fix is upstream: lower the column's cardinality, pre-aggregate, or in DirectQuery use APPROXIMATEDISTINCTCOUNT (DirectQuery only). The professional habit is to confirm with Performance Analyzer and DAX query view whether time is going to the storage engine (a model/cardinality issue) or the formula engine (a DAX-logic issue) before deciding which lever to pull.

A Diagnostic-First Mindset

Strong optimizers resist guessing. Before rewriting anything, capture the visual's query with Performance Analyzer and run it in DAX query view (or DAX Studio) to see whether the cost is storage-engine time (large scans, high cardinality, distinct counts) or formula-engine time (row-by-row iteration, repeated re-evaluation). Storage-engine-bound queries are usually model problems, fixed by lowering cardinality, pre-aggregating, or removing columns; formula-engine-bound queries are usually DAX problems, fixed by VARs, column predicates instead of table FILTERs, and SUM instead of needless SUMX.

Applying one targeted change and re-measuring, rather than bundling several speculative edits, is both better engineering and the disciplined approach the exam's optimization scenarios assume. The recurring lesson is that the same symptom (a slow visual) can have opposite cures depending on which engine is doing the work.