DAX Optimization Patterns

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 patterns can reduce measure evaluation time by 10-100x.

Variables Eliminate Redundancy

Before (expression evaluated twice):

Profit Margin =
DIVIDE(
    SUM(Sales[Revenue]) - SUM(Sales[Cost]),
    SUM(Sales[Revenue])
)

After (expression 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):

Premium Sales =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(Products, Products[Price] > 100)
)

Fast (direct column filter uses storage engine):

Premium Sales =
CALCULATE(
    SUM(Sales[Amount]),
    Products[Category] = "Premium"
)

Rule of thumb: Use FILTER() only when you need to reference a measure in the filter condition. For simple column comparisons, use direct filters.

Avoid Unnecessary Iterators

Slow (SUMX iterates every row):

Total Revenue = SUMX(Sales, Sales[Amount])

Fast (SUM uses the storage engine directly):

Total Revenue = SUM(Sales[Amount])

Use SUMX only when you need row-level computation:

// SUMX is needed here — Quantity * Price must be calculated per row
Total Revenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])

SELECTEDVALUE vs. Complex Patterns

Complex pattern:

Selected Region =
CALCULATE(
    MAX(Regions[RegionName]),
    FILTER(Regions, COUNTROWS(Regions) = 1)
)

Simple and efficient:

Selected Region = SELECTEDVALUE(Regions[RegionName], "Multiple")

Conditional Aggregation Patterns

Inefficient (IF with separate calculations):

Active Sales =
IF(
    HASONEVALUE(Products[Status]),
    IF(VALUES(Products[Status]) = "Active",
        SUM(Sales[Amount]), 0),
    SUM(Sales[Amount])
)

Efficient (CALCULATE with filter):

Active Sales =
CALCULATE(SUM(Sales[Amount]), Products[Status] = "Active")

On the Exam

The PL-300 frequently tests:

• Recognizing inefficient DAX patterns and suggesting improvements
• Understanding when FILTER() is necessary vs. direct column filters
• Using variables to improve readability and performance
• Choosing between SUM and SUMX appropriately
• Understanding storage engine vs. formula engine implications