DAX Mastery Skill

Based on “The Definitive Guide to DAX” by Alberto Ferrari and Marco Russo. This skill enables writing expert-level DAX code and debugging DAX issues.

---

Core Concepts

Data Model Fundamentals

• DAX operates on a data model: a set of tables linked by relationships
• Relationships have a one-side and a many-side; filtering flows from one-side to many-side by default
• Bidirectional relationships allow filtering in both directions (use with caution)
• Relationships are created on single columns only (no composite keys)
• All relationships in DAX are LEFT OUTER JOINS, applied automatically
• The column used on the one-side must have unique values (it is the key)

DAX Data Types

• Integer (64-bit signed)
• Decimal (64-bit double precision floating point)
• Currency (fixed decimal, stored as 64-bit integer internally - important for VertiPaq optimization)
• DateTime (floating point: integer part = date, decimal part = time)
• Boolean (TRUE/FALSE)
• String (Unicode)
• BLANK (similar to NULL, but with special propagation rules)

DAX is a Functional Language

• Everything is an expression (no statements, loops, or jumps)
• Functions return either scalar values or tables
• Table functions can be nested; innermost evaluates first (except CALCULATE/CALCULATETABLE which evaluate filter arguments before the first argument)
• Column references always use TableName[ColumnName] syntax
• Measure references omit the table name: [MeasureName]

---

Evaluation Contexts

This is THE most important concept in DAX. Every DAX expression is evaluated within two concurrent contexts.

Filter Context

• A set of filters applied to the data model that determine which rows are visible
• Each filter operates on columns (not tables) - a list of active values for each column
• Filters from rows, columns, slicers, page filters, and query filters all combine via AND
• Filter context propagates through relationships from one-side to many-side
• Definition: “A filter context is a set of tables. Each table contains a single column and lists all the values for that column which the engine considers visible in the current context.”

Row Context

• Contains a single row and is used to determine column values during iteration
• Created automatically in:
  • Calculated columns (iterates each row of the table)
  • Iterator functions (SUMX, AVERAGEX, FILTER, ADDCOLUMNS, etc.)
• Row context does NOT automatically propagate through relationships
• Multiple row contexts can exist simultaneously (nested iterators)
• When iterating the same table in nested contexts, the inner row context hides the outer one

Critical Rules

1. Both contexts always exist simultaneously - a formula’s result depends on both
2. Aggregate functions (SUM, MIN, MAX) use only the filter context - they ignore the row context
3. Column references use the row context to get the value for the current row
4. A column reference in a measure (where no row context exists) causes an error
5. Row context does NOT filter related tables - only filter context does

Context Transition

• CALCULATE transforms any existing row context into an equivalent filter context
• This is called context transition and is one of DAX’s most powerful features
• After context transition, the filter context propagates through relationships (unlike row context)
• Measure references automatically wrap in CALCULATE - so calling a measure inside an iterator always triggers context transition
• If a table has a primary key, context transition filters only that key column (one row)
• If no primary key, context transition filters ALL columns (may match multiple rows if duplicates exist)

Example of context transition:

-- In a calculated column on Product table:
Product[SalesAmount] = SUM ( Sales[SalesAmount] )           -- Returns grand total (no context transition)
Product[SalesAmountCalc] = CALCULATE ( SUM ( Sales[SalesAmount] ) )  -- Returns sales for THIS product (context transition)

Automatic context transition with measures:

[SumOfSalesAmount] := SUM ( Sales[SalesAmount] )

-- These two are IDENTICAL because measures auto-wrap in CALCULATE:
Product[Sales] = CALCULATE ( [SumOfSalesAmount] )
Product[Sales] = [SumOfSalesAmount]

---

CALCULATE and CALCULATETABLE

CALCULATE is the single most important function in DAX. It is the ONLY function that can modify the filter context.

Syntax

CALCULATE ( <Expression>, <Filter1>, <Filter2>, ... <FilterN> )
CALCULATETABLE ( <TableExpression>, <Filter1>, <Filter2>, ... <FilterN> )

Evaluation Algorithm

1. Takes the current filter context and copies it
2. Performs context transition (transforms any row contexts into filter context)
3. Evaluates each filter argument (in the original filter context, NOT the new one)
4. If multiple filter arguments affect the same column, they are intersected (AND)
5. Replaces existing filters on affected columns with new filters
6. Evaluates the expression in the new filter context
7. Restores the original filter context and returns the result

Filter Argument Types

Boolean conditions (single column only):

CALCULATE ( SUM ( Sales[Amount] ), Product[Color] = "Red" )
-- Automatically converted to:
CALCULATE ( SUM ( Sales[Amount] ), FILTER ( ALL ( Product[Color] ), Product[Color] = "Red" ) )

• Boolean conditions replace existing filters on that column (they use ALL internally)
• Cannot reference multiple columns in a single Boolean condition

Table expressions (FILTER with multiple columns):

CALCULATE (
    SUM ( Sales[Amount] ),
    FILTER ( Product, Product[UnitPrice] >= Product[UnitCost] * 2 )
)

• FILTER iterates in the original filter context (not the new one CALCULATE creates)
• The table returned replaces filters on ALL columns present in that table

Critical: Filter Evaluation Order

• Filter arguments are evaluated BEFORE CALCULATE creates the new context
• FILTER inside CALCULATE sees the original filter context
• To ignore existing filters in FILTER, use ALL:

CALCULATE (
    SUM ( Sales[Amount] ),
    FILTER ( ALL ( Product ), Product[UnitPrice] >= Product[UnitCost] * 2 )
)

Context Transition Precedence

• Context transition happens before filter arguments are applied
• Filter arguments can override the filter created by context transition:

-- In a calculated column on Product:
Product[SumOfAllUnitPrice] = CALCULATE ( SUM ( Product[UnitPrice] ), ALL ( Product ) )
-- Returns grand total because ALL(Product) overrides context transition

KEEPFILTERS Modifier

CALCULATE (
    SUM ( Sales[Amount] ),
    KEEPFILTERS ( Product[Color] = "Red" )
)

• KEEPFILTERS intersects the new filter with the existing one instead of replacing it
• Result: shows sales only when the existing filter already includes Red; blank otherwise

---

Variables (VAR / RETURN)

Syntax

VAR <VariableName> = <Expression>
[VAR <VariableName2> = <Expression2>]
RETURN <ResultExpression>

Key Rule: Variables Are Evaluated in the Context Where They Are DEFINED, Not Where They Are USED

[HighSalesProducts] :=
VAR TenPercentOfSales = [SalesAmount] * 0.1   -- Evaluated in OUTER context
RETURN
    COUNTROWS (
        FILTER (
            Product,
            [SalesAmount] >= TenPercentOfSales  -- Used INSIDE iteration, but value is from outer context
        )
    )

• This is extremely powerful for referencing values from a previous evaluation context inside an iteration
• Variables avoid redundant computation of the same expression
• Variables can hold both scalar values and tables
• Using VAR makes code more readable and often faster

---

Iterators (X-Functions)

How Iterators Work

1. Receive a table as the first argument
2. Create a new row context for each row of that table
3. Evaluate the expression (second argument) in the new row context + any existing contexts
4. Aggregate the results

Common Iterators

Function	Aggregation
SUMX ( <table>, <expression> )	Sum
AVERAGEX ( <table>, <expression> )	Average
MINX ( <table>, <expression> )	Minimum
MAXX ( <table>, <expression> )	Maximum
COUNTX ( <table>, <expression> )	Count non-blank
COUNTAX ( <table>, <expression> )	Count non-blank (incl. text)
CONCATENATEX ( <table>, <expr>, <delimiter> )	Concatenate strings
RANKX ( <table>, <expression> )	Rank
PRODUCTX ( <table>, <expression> )	Product (multiplication)

FILTER Is an Iterator

FILTER ( <table>, <condition> )

• Iterates <table>, evaluates <condition> for each row, returns rows where condition is TRUE
• The <table> is evaluated in the current context
• Nesting FILTER calls: innermost executes first; put the most selective filter innermost for performance

Key Iterator Patterns

-- Weighted average
[WeightedAvg] :=
SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] ) / SUM ( Sales[Quantity] )

-- Conditional aggregation
[LargeSales] :=
SUMX (
    FILTER ( Sales, Sales[Amount] > 1000 ),
    Sales[Amount]
)

-- Distinct count with condition
[CustomersAboveAvg] :=
VAR AvgSales = [TotalSales] / COUNTROWS ( Customer )
RETURN
COUNTROWS (
    FILTER ( Customer, [TotalSales] > AvgSales )
)

---

Table Functions Reference

Filter Functions

Function	Description
FILTER ( <table>, <condition> )	Returns rows satisfying condition
ALL ( <table or column> )	Returns all rows/values ignoring filters
ALLEXCEPT ( <table>, <col1>, <col2>... )	Removes filters from all columns EXCEPT specified ones
ALLSELECTED ( [<table or column>] )	Restores filters to the “visual total” level
ALLNOBLANKROW ( <table or column> )	Like ALL but excludes the blank row for unmatched relationships
VALUES ( <column or table> )	Returns visible unique values (includes blank for unmatched)
DISTINCT ( <column> )	Returns visible unique values (excludes blank for unmatched)
SELECTEDVALUE ( <column> [, <alternate>] )	Returns the value if column has exactly one visible value
HASONEVALUE ( <column> )	TRUE if column has exactly one visible value
ISFILTERED ( <column> )	TRUE if column is directly filtered
ISCROSSFILTERED ( <column> )	TRUE if column is filtered (directly or via relationship)
KEEPFILTERS ( <expression> )	Intersects instead of replaces in CALCULATE filter arguments

Projection Functions

Function	Description
ADDCOLUMNS ( <table>, <name>, <expr>, ... )	Adds calculated columns to a table
SELECTCOLUMNS ( <table>, <name>, <expr>, ... )	Selects/creates specific columns
ROW ( <name>, <expr>, ... )	Creates a single-row table

Grouping/Joining Functions

Function	Description
SUMMARIZE ( <table>, <groupByCol>, ... )	Groups by columns (use ADDCOLUMNS for calculated columns)
SUMMARIZECOLUMNS ( <groupByCol>, ..., <filterTable>, ..., <name>, <expr>, ... )	Preferred for queries; auto-removes blank rows
GROUPBY ( <table>, <groupByCol>, ..., <name>, <CURRENTGROUP expr> )	Groups with CURRENTGROUP aggregation
CROSSJOIN ( <table1>, <table2>, ... )	Cartesian product
UNION ( <table1>, <table2>, ... )	Combines tables vertically
INTERSECT ( <table1>, <table2> )	Returns rows in both tables
EXCEPT ( <table1>, <table2> )	Returns rows in table1 not in table2
NATURALINNERJOIN ( <table1>, <table2> )	Inner join on common columns
NATURALLEFTOUTERJOIN ( <table1>, <table2> )	Left outer join on common columns

Utility Functions

Function	Description
TOPN ( <n>, <table>, <orderExpr> [, <order>] )	Returns top N rows (may return more on ties)
GENERATE ( <table1>, <table2Expr> )	Cross apply: evaluates table2 for each row of table1
GENERATEALL ( <table1>, <table2Expr> )	Like GENERATE but keeps rows where table2 is empty
TREATAS ( <table>, <column1>, ... )	Applies table as filter by mapping to specified columns
DATATABLE ( <name>, <type>, ... )	Creates a literal table inline

Relationship Functions

Function	Description
RELATED ( <column> )	Gets value from many-to-one related table (requires row context)
RELATEDTABLE ( <table> )	Gets rows from one-to-many related table (requires row context)
USERELATIONSHIP ( <col1>, <col2> )	Activates an inactive relationship in CALCULATE
CROSSFILTER ( <col1>, <col2>, <direction> )	Changes cross-filter direction in CALCULATE

---

Column Lineage

• Every column returned by a table function has a lineage - an internal reference to the physical column
• Lineage determines whether a column can participate in filter context propagation
• Renaming a column preserves lineage (SELECTCOLUMNS with simple column reference)
• Any expression breaks lineage (even Product[Key] + 0 loses lineage to Product[Key])
• Columns without lineage (from ADDCOLUMNS calculations) cannot be used as CALCULATE filter arguments
• TREATAS can assign lineage to columns that lack it

---

Time Intelligence Functions

Prerequisites

1. A Date table with one row per day, covering all years completely (Jan 1 - Dec 31)
2. The table must be marked as a Date table in the model
3. The Date column must have a Date/DateTime data type

Building a Date Table

-- Auto-detect date range from all date columns
Date = CALENDARAUTO ()
Date = CALENDARAUTO ( 6 )  -- Fiscal year ending June 30

-- Explicit date range
Date = CALENDAR (
    DATE ( YEAR ( MIN ( Sales[OrderDate] ) ), 1, 1 ),
    DATE ( YEAR ( MAX ( Sales[OrderDate] ) ), 12, 31 )
)

-- Common calculated columns for Date table
'Date'[Year] = YEAR ( 'Date'[Date] )
'Date'[MonthNumber] = MONTH ( 'Date'[Date] )
'Date'[Month] = FORMAT ( 'Date'[Date], "MMMM" )
'Date'[Quarter] = "Q" & INT ( FORMAT ( 'Date'[Date], "Q" ) )
'Date'[YearMonth] = FORMAT ( 'Date'[Date], "MMMM" ) & " " & YEAR ( 'Date'[Date] )

Year-to-Date / Quarter-to-Date / Month-to-Date

[YTD Sales] := TOTALYTD ( [Sales Amount], 'Date'[Date] )
[YTD Sales] := CALCULATE ( [Sales Amount], DATESYTD ( 'Date'[Date] ) )

[QTD Sales] := TOTALQTD ( [Sales Amount], 'Date'[Date] )
[MTD Sales] := TOTALMTD ( [Sales Amount], 'Date'[Date] )

-- Fiscal year ending June 30
[Fiscal YTD] := TOTALYTD ( [Sales Amount], 'Date'[Date], "06-30" )

Prior Period Comparisons

-- Same period last year
[PY Sales] := CALCULATE ( [Sales Amount], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
-- Equivalent using DATEADD
[PY Sales] := CALCULATE ( [Sales Amount], DATEADD ( 'Date'[Date], -1, YEAR ) )

-- Previous quarter/month/day
[PQ Sales] := CALCULATE ( [Sales Amount], DATEADD ( 'Date'[Date], -1, QUARTER ) )
[PM Sales] := CALCULATE ( [Sales Amount], DATEADD ( 'Date'[Date], -1, MONTH ) )

-- Full previous year total (not shifted period)
[PY Total] := CALCULATE ( [Sales Amount], PARALLELPERIOD ( 'Date'[Date], -1, YEAR ) )

Year-over-Year

[YOY Sales] := [Sales Amount] - [PY Sales]
[YOY Sales %] := DIVIDE ( [YOY Sales], [PY Sales] )

-- YTD Year-over-Year
[PY YTD Sales] := CALCULATE ( [Sales Amount], DATESYTD ( SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) )
[YOY YTD Sales] := [YTD Sales] - [PY YTD Sales]
[YOY YTD Sales %] := DIVIDE ( [YOY YTD Sales], [PY YTD Sales] )

-- YTD over Previous Year Total (KPI)
[YTDOPYT %] := DIVIDE ( [YTD Sales], [PY Total] )

Moving Annual Total

[MAT Sales] :=
CALCULATE (
    [Sales Amount],
    DATESINPERIOD ( 'Date'[Date], LASTDATE ( 'Date'[Date] ), -1, YEAR )
)

Running Total

[Running Total] :=
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

Semi-Additive Measures (Balance/Inventory)

-- Last known balance: use LASTNONBLANK for sparse data
[On Hand Quantity] :=
CALCULATE (
    SUM ( Inventory[Quantity] ),
    LASTNONBLANK (
        'Date'[Date],
        CALCULATE ( COUNTROWS ( Inventory ), ALL ( Product ) )
    )
)

-- Per-product last known balance (for sparse per-product data)
[Balance by Product] :=
IF (
    CALCULATE ( COUNTROWS ( Inventory ), ALL ( Product ) ) > 0,
    SUMX (
        Product,
        CALCULATE (
            SUM ( Inventory[Quantity] ),
            LASTNONBLANK (
                DATESBETWEEN ( 'Date'[Date], BLANK (), LASTDATE ( 'Date'[Date] ) ),
                CALCULATE ( COUNTROWS ( Inventory ) )
            )
        )
    )
)

Working with Multiple Dates

Option 1: Inactive relationships + USERELATIONSHIP

[Delivered Amount] :=
CALCULATE (
    [Sales Amount],
    USERELATIONSHIP ( Sales[DeliveryDateKey], 'Date'[DateKey] )
)

Option 2: Multiple Date tables (role-playing dimensions)

• Physically duplicate the Date table with different names (OrderDate, DeliveryDate)
• Rename columns with prefixes (OY 2024, DY 2024) for clarity
• Each table has its own active relationship

Time Intelligence Nesting Rule

When nesting time intelligence functions, put functions that restrict the range (like LASTDATE) innermost, and functions that shift dates (like SAMEPERIODLASTYEAR) outermost. This avoids issues with dates outside the Date table range returning BLANK.

-- CORRECT order:
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) )

-- RISKY order (may fail at table boundaries):
SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE ( 'Date'[Date] ) ) )

---

Common Patterns

Percentage of Total

[% of Total] :=
DIVIDE (
    [Sales Amount],
    CALCULATE ( [Sales Amount], ALL ( Product ) )
)

-- Percentage of parent category (keeping slicer filters)
[% of Category] :=
DIVIDE (
    [Sales Amount],
    CALCULATE (
        [Sales Amount],
        ALL ( Product[Subcategory] ),
        ALL ( Product[ProductName] )
    )
)

Percentage of Selection (Visual Total)

[% of Selection] :=
DIVIDE (
    [Sales Amount],
    CALCULATE ( [Sales Amount], ALLSELECTED () )
)

Customers Who Bought Above Average

[Sales Above Avg] :=
VAR AvgSales = AVERAGEX ( Customer, [Sales Amount] )
RETURN
SUMX (
    Customer,
    IF ( [Sales Amount] > AvgSales, [Sales Amount] )
)

New vs Returning Customers

[New Customers] :=
VAR CurrentPeriodEnd = MAX ( 'Date'[Date] )
RETURN
COUNTROWS (
    FILTER (
        Customer,
        CALCULATE (
            MIN ( Sales[OrderDate] ),
            ALL ( 'Date' )
        ) >= MIN ( 'Date'[Date] )
        && CALCULATE (
            MIN ( Sales[OrderDate] ),
            ALL ( 'Date' )
        ) <= CurrentPeriodEnd
    )
)

ABC Analysis / Pareto

-- Rank products by sales
[Product Rank] :=
IF (
    HASONEVALUE ( Product[ProductName] ),
    RANKX ( ALL ( Product[ProductName] ), [Sales Amount] )
)

-- Cumulative percentage for Pareto
[Cumulative %] :=
VAR CurrentSales = [Sales Amount]
RETURN
DIVIDE (
    SUMX (
        FILTER (
            ALL ( Product[ProductName] ),
            [Sales Amount] >= CurrentSales
        ),
        [Sales Amount]
    ),
    CALCULATE ( [Sales Amount], ALL ( Product[ProductName] ) )
)

Many-to-Many Relationships (Bridge Tables)

-- When a bridge table connects two dimensions
[Filtered Measure] :=
CALCULATE (
    [Sales Amount],
    FILTER (
        BridgeTable,
        RELATED ( Dimension[Column] ) = "Value"
    )
)

Virtual Relationships with TREATAS

[Sales by Budget Category] :=
CALCULATE (
    [Sales Amount],
    TREATAS ( VALUES ( Budget[Category] ), Product[Category] )
)

Percentage Over Hierarchies

[PercOfParent] :=
IF (
    ISFILTERED ( Product[ProductName] ),
    DIVIDE ( [Sales Amount], CALCULATE ( [Sales Amount], ALL ( Product[ProductName] ) ) ),
    IF (
        ISFILTERED ( Product[Subcategory] ),
        DIVIDE ( [Sales Amount], CALCULATE ( [Sales Amount], ALL ( Product[Subcategory] ) ) ),
        IF (
            ISFILTERED ( Product[Category] ),
            DIVIDE ( [Sales Amount], CALCULATE ( [Sales Amount], ALL ( Product[Category] ) ) )
        )
    )
)

• Test levels from innermost to outermost (product -> subcategory -> category)
• For robustness with slicers, duplicate hierarchy columns as hidden “H columns” and build the hierarchy on those

Parent-Child Hierarchies

-- Flatten P/C hierarchy
PC[HPath] = PATH ( PC[NodeId], PC[ParentNodeId] )
PC[Level1] = LOOKUPVALUE ( PC[Node], PC[NodeId], PATHITEM ( PC[HPath], 1, INTEGER ) )
PC[Level2] =
IF (
    PATHLENGTH ( PC[HPath] ) >= 2,
    LOOKUPVALUE ( PC[Node], PC[NodeId], PATHITEM ( PC[HPath], 2, INTEGER ) ),
    PC[Level1]
)
-- Repeat for each level...

PC[NodeDepth] = PATHLENGTH ( PC[HPath] )

-- Measure that hides invalid rows
[PC Amount] :=
IF (
    MAX ( PC[NodeDepth] ) >= [BrowseDepth],
    SUM ( Sales[Amount] )
)

---

Performance Optimization

VertiPaq Engine Architecture

Columnar storage: Each column is stored separately in memory. Scanning a single column is very fast. Multi-column operations require more CPU to rearrange data.

Compression types:

1. Value encoding: Subtracts minimum value to reduce bits per value (integers/currency only)
2. Dictionary encoding: Builds a dictionary of distinct values, stores indexes instead of values. Makes all data types equivalent in performance.
3. Run Length Encoding (RLE): Stores repeating adjacent values as (value, count) pairs. Sorting data dramatically improves RLE compression.

Key principle: The #1 factor for column size is CARDINALITY (number of distinct values), not data type.

Materialization

• When VertiPaq cannot resolve a query on compressed columns alone, it builds temporary uncompressed tables = materialization
• Materialized tables can be huge and slow
• Single-column scans and simple cross-table filters via relationships do NOT require materialization
• Aggregating pairs of columns from the same table (e.g., SUMMARIZE by two columns) requires materialization

Data Model Optimization Best Practices

1. Reduce column cardinality - the single most important optimization

   • Round decimal values (e.g., 2 decimal places instead of 15)
   • Split DateTime into separate Date and Time columns
   • Replace high-cardinality text with integer keys
   • Remove unnecessary columns entirely

2. Improve sort order for RLE compression

   • Columns with fewer distinct values compress better when sorted
   • VertiPaq automatically finds optimal sort order, but may time-box the search

3. Avoid calculated columns when possible

   • Calculated columns are stored uncompressed and computed during refresh
   • Prefer measures or Power Query transformations

4. Use Star Schema design

   • Fact tables (many rows, few columns) + Dimension tables (few rows, many columns)
   • Relationships should go from dimension (one-side) to fact (many-side)

5. Minimize bidirectional relationships - they can cause ambiguous filter paths and performance issues

Query Optimization Best Practices

1. Use SUMMARIZECOLUMNS instead of SUMMARIZE for EVALUATE queries
2. Never use SUMMARIZE to add calculated columns - use ADDCOLUMNS + SUMMARIZE instead:

   -- BAD
   SUMMARIZE ( Sales, 'Date'[Year], "Total", SUM ( Sales[Amount] ) )
   
   -- GOOD
   ADDCOLUMNS (
       SUMMARIZE ( Sales, 'Date'[Year] ),
       "Total", CALCULATE ( SUM ( Sales[Amount] ) )
   )

3. Put the most selective FILTER innermost when nesting FILTERs
4. Avoid iterating large tables when a simple CALCULATE with filter arguments suffices
5. Use variables to avoid redundant calculations
6. Prefer Boolean conditions in CALCULATE over FILTER when filtering a single column (less materialization)
7. Avoid FILTER on the entire table in CALCULATE when you only need to filter one column - use Boolean condition or FILTER ( ALL ( Table[Column] ), ... ) instead

Hardware Priorities for VertiPaq (in order)

1. CPU Clock Speed (3+ GHz recommended; gaming machines outperform many servers)
2. Memory Speed (1600+ MHz, faster is better)
3. Number of Cores (parallelism only helps for tables with 64M+ rows due to 8M row segments)
4. Memory Size (enough to avoid paging; more RAM doesn’t make queries faster)
5. Disk I/O is irrelevant (all data is in RAM)

---

Anti-Patterns and Common Mistakes

1. Using SUM in a Calculated Column Expecting Row-Level Values

-- WRONG: Returns grand total for every row
Sales[Total] = SUM ( Sales[Amount] )

-- CORRECT: Use CALCULATE for context transition
Sales[Total] = CALCULATE ( SUM ( Sales[Amount] ) )
-- Or just reference the column directly if you want the row value:
Sales[Total] = Sales[Amount]

2. Using Column References in Measures

-- ERROR: Cannot use bare column reference in a measure (no row context)
[Margin] := Sales[Amount] - Sales[Cost]

-- CORRECT: Use aggregation functions
[Margin] := SUM ( Sales[Amount] ) - SUM ( Sales[Cost] )

3. Confusing FILTER in CALCULATE with Boolean Conditions

-- These are NOT equivalent:
-- Boolean: Ignores existing filter on Color, shows sales for Red regardless
CALCULATE ( [Sales], Product[Color] = "Red" )

-- FILTER with Product: Keeps existing filter context, only returns Red if already visible
CALCULATE ( [Sales], FILTER ( Product, Product[Color] = "Red" ) )

-- To make FILTER behave like Boolean:
CALCULATE ( [Sales], FILTER ( ALL ( Product[Color] ), Product[Color] = "Red" ) )

4. Calling a Measure Inside an Iterator Without Understanding Context Transition

-- WRONG: [AverageSales] undergoes context transition, computing average for ONE customer
[Wrong] :=
SUMX (
    Customer,
    IF ( [Sales] > [AverageSales], [Sales] )  -- [AverageSales] is per-customer here!
)

-- CORRECT: Use a variable to capture the value BEFORE the iteration
[Correct] :=
VAR AvgSales = [AverageSales]
RETURN
SUMX ( Customer, IF ( [Sales] > AvgSales, [Sales] ) )

5. Using ALL on Entire Table When You Only Need One Column

-- WRONG: Removes ALL filters from Product (including Category on rows)
CALCULATE ( [Sales], FILTER ( ALL ( Product ), Product[Color] = "Red" ) )

-- CORRECT: Only remove filter on Color
CALCULATE ( [Sales], Product[Color] = "Red" )
-- Or explicitly:
CALCULATE ( [Sales], FILTER ( ALL ( Product[Color] ), Product[Color] = "Red" ) )

6. Wrong Sales from Duplicate Rows with Context Transition

-- If Sales table has duplicate rows (no primary key):
[Wrong Sales] := SUMX ( Sales, [Sales Amount] )  -- Overcounts due to context transition matching multiple rows
[Correct Sales] := SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] )  -- No context transition

7. Using SUMMARIZE to Add Calculated Columns

• SUMMARIZE with column expressions has known bugs and performance issues
• Always use ADDCOLUMNS wrapping SUMMARIZE instead

8. Not Marking the Date Table

• Time intelligence functions require the Date table to be marked with “Mark as Date Table”
• Without it, filters on the Date column do not automatically remove filters on other Date table columns

9. Incomplete Date Tables

• Every year must contain ALL days (Jan 1 - Dec 31)
• Missing days cause time intelligence functions to return wrong results

10. Circular Dependencies in Calculated Columns

• CALCULATE in calculated columns can create subtle circular dependencies because context transition filters columns
• If column A uses CALCULATE referencing column B, and column B depends on column A, you get a circular dependency error

---

Decision Frameworks

Calculated Column vs. Measure

Use Calculated Column When	Use Measure When
Value depends only on the current row	Value depends on filter context (report selections)
Need to use the value in a slicer/filter	Need dynamic aggregation
Need to sort by the value	Value changes based on what user selects
Result is fixed per row regardless of report	Computing percentages, ratios, running totals
Creating a hierarchy level	Computing time intelligence

Default: Use measures. Calculated columns consume memory and are recalculated during refresh. Measures are computed on-the-fly and are more flexible.

CALCULATE vs. Iterators

Use CALCULATE When	Use Iterators When
Changing the filter context	Need row-by-row computation
Computing percentage of total/parent	Expression involves multiple columns from same row
Applying time intelligence	Need conditional aggregation with complex logic
Overriding user selections	Computing weighted averages
Using inactive relationships	Need to rank or compare individual items

Boolean Filter vs. FILTER in CALCULATE

Use Boolean Product[Color] = "Red"	Use FILTER ( ALL/table, condition )
Single-column condition	Multi-column condition
Want to override existing filter on that column	Want to preserve existing filter context
Simpler, more readable	Need OR conditions across columns
Better performance (less materialization)	Complex expressions

ALL vs. ALLEXCEPT vs. ALLSELECTED

Function	Use Case
ALL ( Table )	Remove ALL filters from table (grand total)
ALL ( Table[Column] )	Remove filter from one column only
ALLEXCEPT ( Table, Table[Col1] )	Remove all filters EXCEPT specified columns
ALLSELECTED ()	Restore to “visual total” (what the user sees as the outer boundary)

VALUES vs. DISTINCT vs. ALL

Function	Includes blank for unmatched?	Respects filter context?
VALUES	Yes	Yes
DISTINCT	No	Yes
ALL	Yes	No (returns all)
ALLNOBLANKROW	No	No (returns all)

---

EVALUATE Query Syntax

[DEFINE
    { MEASURE <TableName>[<MeasureName>] = <Expression> }
    { VAR <VariableName> = <Expression> }
]
EVALUATE <TableExpression>
[ORDER BY { <Expression> [ASC | DESC] } [, ...]]
[START AT { <Value> } [, ...]]

• DEFINE MEASURE creates measures local to the query (great for debugging)
• Local measures override model measures only in the EVALUATE expression, not in other measures
• DEFINE VAR creates query-scoped variables
• ORDER BY controls sort order (ASC is default)
• START AT enables pagination

-- Debug a measure by overriding it locally
DEFINE
    MEASURE Sales[Test Sales] = SUMX ( Sales, Sales[Quantity] * Sales[NetPrice] )
EVALUATE
ADDCOLUMNS (
    VALUES ( Product[Brand] ),
    "Sales", [Test Sales]
)
ORDER BY [Sales] DESC

---

DAX Formatting Best Practices

1. One function per line for readability
2. Indent nested functions
3. Place each CALCULATE filter argument on its own line
4. Use TableName[ColumnName] for columns, [MeasureName] for measures (no table prefix)
5. Use := for measure definitions, = for calculated columns
6. Use meaningful names; prefix with the purpose (e.g., [YTD Sales], [PY Sales])
7. Use variables to break complex expressions into readable steps