top of page

Optimize DAX

Optimizing your DAX code can be a challenging (even monumental) task. In this page, I've laid out four ways you can optimize your DAX code to run as efficiently as possible. Keep in mind, however, performance varies greatly across machines and models.

Create an image of an infinite loop. Draw it in a futuristic style..jpg

01

Remove context transition within iterations

Whenever you reference another measure, you are invoking context transition (an implicit CALCULATE() is placed over the measure). While this is fine in most scenarios, it becomes a huge bottleneck when used within a large iteration (>5000 rows).

DEFINE MEASURE

Sales[Adjusted Sales]=

SUMX(

Sales,

Sales[Net Price] * Sales[Quantity] * 1.02

)

DEFINE MEASURE

Sales[Adjusted Sales]=

SUMX(

Sales,

[Sales Amount] * 1.02

)

02

Avoid time intelligence functions

Time intelligence is convenient but behind the scenes it requires materializing each date within the filter context. Creating your own custom logic to define date ranges in your measure can generally improve performance.

Draw an abstract image of time. Make it in a futuristic style. Use grey scale..jpg

DEFINE MEASURE

Sales[Sales Previous Month]=

CALCULATE(

[Sales Amount],

PREVIOUSMONTH(Date[Date])

)

DEFINE MEASURE

Sales[Sales Previous Month]=

CALCULATE(

[Sales Amount],

Date[Year Month Index] = MAX( Date[Year Month Index]) - 1

)

Draw an abstract image of the a brain. Use a futuristic style in grey scale..jpg

03

Push logic away

Conditional logic like IF and SWITCH cannot be used by the storage engine (which is very fast) and is handled instead by the formula engine (which is slow). Re-writing your condition to be a filter will always be better. If you're unable to do this, "pushing" the logic to the outermost iteration is a best practice.

DEFINE MEASURE

Sales[Big Quantity Sales]=

SUMX(

Sales,

IF ( Sales[Quantity] > 3, Sales[Net Price] * Sales[Quantity])

)

DEFINE MEASURE

Sales[Big Quantity Sales]=

CALCULATE(

[Sales Amount],

Sales[Quantity] > 3

)

04

Create a calculated column for very complex filters

If your logic is very complex (uses multiple columns, and, or, >, <, etc.) it may be better to create a calculated column to handle the logic at refresh time instead of at calculation time. Your model size will increase but is usually a small price to pay for better query performance.

Draw an 2D image of complex, interconnected nodes. Use grey scale..jpg

DEFINE MEASURE

Sales[Targeted Customer Sales]=

CALCULATE(

[Sales Amount],

    FILTER(

        ALL( DimCustomer ),

        FIND( "500", [Phone], 1, -1) <> -1

            && [CustomerType] = "Person"

            && [YearlyIncome] > 50000

            && [DateFirstPurchase] >= DATE( 2003, 1, 1)

    )

)

DEFINE MEASURE

Sales[Targeted Customer Sales]=

CALCULATE(

[Sales Amount],

Customer[Targeted Customer] = 1 //Logic pushed to customer table

)

IF.EAGER_Finished.png

05

Use IF.EAGER()

If you're testing condition A and returning either B or C, you may try out IF.EAGER() instead of the regular IF(). This will be particularly helpful if you are slicing and dicing by many columns in your visuals (or a high cardinality column).

DEFINE MEASURE

Sales[Average Product Sales]=

AVERAGEX(

VALUES ( Product ),

    IF([Condition A], [United States Sales], [European Sales] )

)

DEFINE MEASURE

Sales[Average Product Sales]=

AVERAGEX(

VALUES ( Product ),

IF.EAGER([Condition A], [United States Sales], [European Sales] )

)

Yet more techniques to try!

  • Filter columns, not tables.

  • Aggregate your data to a larger grain.

  • ​Avoid many-many relationships.

  • Avoid filtering on large cardinality columns.

  • Invert any nested iterations.

  • SUMX over DISTINCT instead of DISTINCTCOUNT.

bottom of page