OPTIMIZING

DAX
General Guidelines & Best Practices
DATA MODEL

Do not import columns you do not need

Star schema is always best

- Resist the urge to normalize your dimension tables
- Remember: Columns will be encoded and compressed,
so you can keep values “as-is” an no need to create separate dimensions
- Avoid many-to-many relationships if possible

Cardinality (unique values) is of the utmost importance - aim to minimize as much as possible
- Split columns (i.e. DateTime should always be a column for Date and one for Time).
This should be done at the source, not after being imported!
- Reduce exactness of data if business requirements allow
- Convert to integers if possible

Relationship size is based on the cardinality of the Primary Key - not

the attribute used in filtering. Cardinality determines the speed.

Find and correct Referential Integrity issues (missing keys in a Dimen-

sion table that are present in the Fact table)

1 Discover Enterprise DNA’s DAX Courses

DATA MODEL

Calculated Columns:
- Try to compute at the source if possible. Can use DAX to create calculated
columns but could get better compression if they are done at the source
- Do not store partial calculations as they take up memory
- Complex filters are good candidates for calculated columns

Everything happens in columns (encoding, compression, scanning, etc.)

2 Discover Enterprise DNA’s DAX Courses

OPTIMIZING

First and foremost: reduce For- Start at the “Server Timings” Always clear the cache before
mula Engine (FE) and increase tab in DAX Studio and the running tests. Can keep the cache
Storage Engine (SE) usage “Duration” column warm to run further test if need
be, but always start with a cold

Try to reduce the amount of Try to mitigate CallBackDataID Look at the # Records being
materialization of data as because queries with given to the FE from the SE in
much as possible CallBackDataID: the PQP. The number of records
here should be as close to the
- Physical Query plan has a amount of records required for
- Are slower than pure SE the output. If the Records
“Records” column. This num-
queries number is very high this can be
ber should be as close to the
- Remove the data caching a sign of early materialization.
number of the output query
feature of the SE Aim for late materialization!
as possible.
- However, if the “Duration” is
small no need to worry
- Date functions (e.g., LAST
Date), Math functions (e.g.,
ROUND), Error Functions

3 Discover Enterprise DNA’s DAX Courses

DATA MEASURES

Avoid measures (or re-write) that have CallBackDataID

Try to breakout complex IF statements into separate measures using CALCULATE.

Be careful with context transition in an iterator. Remember: a measure has an implied calculate
wrapped around which invokes context transition.

Variables help with readability/debugging/performance. Remember: variables are computed once where they
are defined, not where they are used. Also, that are immutable (cannot be changed)

Use CALCULATE/CALCULATETABLE over FILTER when possible (look for a “WHERE” clause in the SE Query

Use SUMMARIZE to group and ADDCOLUMNS to add calculated columns to that table

Use HASONEFILTER/HASONEVALUE to detect filters

4 Discover Enterprise DNA’s DAX Courses

VERTIPAQ OPERATOR TYPES
LOGICAL PLAN OPERATORS OPERATOR TYPE WHAT IT DOES EXAMPLE

Scan_Vertipaq RelLogOp - Basis of all other VertiPaq operators

- Performs joins following M:1
- Determines which rows to return
- Groups results by output columns

GroupBy_Ver- RelLogOp - Renames Columns

- Adds rollup columns to VertiPaq
- Query

Filter_Vertipaq RelLogOp Adds a filter predicate to a query

Sum_Vertipaq ScaLogOp Adds a Sum to a VertiPaqQuery

5 Discover Enterprise DNA’s DAX Courses

VERTIPAQ OPERATOR TYPES
LOGICAL PLAN OPERATORS OPERATOR TYPE WHAT IT DOES EXAMPLE

Min_Vertipaq RelLogOp Adds a filter predicate to a query

Max_Vertipaq ScaLogOp Adds a Max to a VertiPaqQuery

Count_Vertipaq ScaLogOp Adds a Count to a VertiPaqQuery

DistinctCount_Vertipaq ScaLogOp Adds a DistinctCount to a VertiPaqQuery

Average_Vertipaq ScaLogOp Adds a Average to a VertiPaqQuery

Stdev.S_Vertipaq ScaLogOp Adds a Standard Deviation to a VertiPaqQuery

Stdev.P_Vertipaq ScaLogOp Adds a Standard Deviation based on Populatio to a VertiPaqQuery

Var.S_Vertipaq ScaLogOp Adds a Variance based on Sample to a VertiPaqQuery

Var.P_Vertipaq ScaLogOp Adds a Variance based on Populatio to a VertiPaqQuery

6 Discover Enterprise DNA’s DAX Courses

 Join the most advanced Power BI community anywhere
Access the best content, resources, on-going training and support available globally for Power BI!

www.enterprisedna.co | info@enterprisedna.co