Dax

DAX

Skills and references for writing, debugging, and optimizing DAX in semantic models.

Optimization

For systematic DAX query performance optimization, read the workflow reference first:

[`references/dax-performance-optimization.md`](./references/dax-performance-optimization.md) — Tiered framework (4 tiers), phased workflow, decision guide, and error handling.

Detailed reference files (progressive disclosure — consult as directed by the workflow):

• [`references/engine-internals.md`](./references/engine-internals.md) — FE/SE architecture, xmSQL, compression/segments, SE fusion, trace diagnostics
• [`references/dax-patterns.md`](./references/dax-patterns.md) — Tier 1 DAX patterns (DAX001–DAX021) + Tier 2 query structure (QRY001–QRY004)
• [`references/model-optimization.md`](./references/model-optimization.md) — Tier 3 model patterns (MDL001–MDL009) + Tier 4 Direct Lake (DL001–DL002)

Trace capture and performance profiling:

• Local models (Power BI Desktop): Use the Tabular Editor CLI te query (see the `te-cli` skill) first; as an alternative, the `connect-pbid` skill covers FE/SE timing (performance-profiling.md) and intermediate result inspection (evaluateandlog-debugging.md).
• Remote models (Fabric Service / XMLA): Run DAX with the Tabular Editor CLI te query (-s <workspace> -d <model>) against the workspace XMLA endpoint; see the `te-cli` skill (tabular-editor plugin).
• Power BI Modeling MCP: also available for trace and query if you prefer an MCP tool; reach for it after the options above.

Related Skills

• `semantic-model` — Model design, build, and auditing including DAX anti-patterns and best practices
• `connect-pbid` (pbi-desktop plugin) — Trace capture, performance profiling, EVALUATEANDLOG debugging
• `lineage-analysis` — Impact analysis before model changes

DAX and Query Structure Patterns

Tier 1 DAX patterns (DAX001-DAX021) and Tier 2 query structure patterns (QRY001-QRY004).

Related references: Engine Internals · Model and Direct Lake Optimization

---

Section 3: Tier 1 DAX Optimization Patterns

Autonomy: Auto-apply freely. Modify only measure/UDF definitions in the DEFINE block. Keep EVALUATE and SUMMARIZECOLUMNS grouping identical.

Candidate optimizations, not guarantees: Each pattern is a hypothesis to test. Cardinality, data layout, relationships, filters, storage mode, and engine folding can make a rewrite help, do nothing, or hurt. Apply one or more matching patterns, validate trace/runtime and semantic equivalence after each step, and continue iterating because one rewrite can expose the next optimization opportunity. Keep changes that help; revert changes that do not.

Prefer SUMMARIZECOLUMNS: Fully supported inside measure definitions — earlier restrictions no longer apply. Use it to replace ADDCOLUMNS/SUMMARIZE patterns (DAX002), pre-materialize context transitions before iterating (DAX006), and cache repeated evaluations into a single virtual table (DAX003). Prefer it over ADDCOLUMNS(VALUES(...), ...) unless a specific scenario prevents it.

DAX001: Use Simple Column Filter Predicates as CALCULATE Arguments

Identifier: FILTER(Table, ...) as a filter argument, or combined predicates. Verify trace; simple cases can fold to WHERE.

Action: Keep filters column-scoped. Replace table filters with boolean predicates, and split && into separate filter arguments.

Anti-pattern — FILTER with table expression uses an iterator:

CALCULATE(
    SUM('Sales'[Amount]),
    FILTER('Product', 'Product'[Category] = "Electronics")
)

Preferred — column predicate, no iterator:

CALCULATE(
    SUM('Sales'[Amount]),
    KEEPFILTERS( 'Product'[Category] = "Electronics")
)

Anti-pattern — `&&` joins predicates into a single iterator argument:

CALCULATETABLE( 'Sales', 'Sales'[Region] = "West" && 'Sales'[Amount] > 1000 )

Preferred — separate predicates for better query plan:

CALCULATETABLE( 'Sales', 'Sales'[Region] = "West", 'Sales'[Amount] > 1000 )

---

DAX002: Use SUMMARIZECOLUMNS for Grouped Calculations

Identifier: grouped calculation built with ADDCOLUMNS/SUMMARIZE. Compare trace against a direct SUMMARIZECOLUMNS shape.

Action: Use SUMMARIZECOLUMNS for grouping + calculation when row shape and filter semantics stay identical.

Anti-patterns:

SUMMARIZE ( 'Sales', 'Sales'[ProductKey], "Total Profit", [Profit] )
ADDCOLUMNS ( SUMMARIZE ( 'Sales', 'Sales'[ProductKey] ), "Total Profit", [Profit] )
ADDCOLUMNS ( 'Sales', "Total Profit", CALCULATE ( [Profit] ) )
ADDCOLUMNS ( VALUES('Sales'[ProductKey]), "Total Profit", [Profit] )

Preferred:

SUMMARIZECOLUMNS ( 'Sales'[ProductKey], "Total Profit", [Profit] )

---

DAX003: Cache Repeated Expressions in Variables

Identifier: repeated measure or expression references. Verify with FE time, repeated SE requests, or cache matches.

Action: Cache repeated or row-independent expressions in variables at the smallest safe scope.

Anti-pattern — repeated measure reference:

VAR TotalA = [Sales Amount] * 1.1
VAR TotalB = [Sales Amount] * 0.9
VAR TotalC = [Sales Amount] + 1000

Preferred:

VAR _SalesAmount = [Sales Amount]
VAR TotalA = _SalesAmount * 1.1
VAR TotalB = _SalesAmount * 0.9
VAR TotalC = _SalesAmount + 1000

Anti-pattern — same measure iterated twice:

VAR A = SUMX ( VALUES('Sales'[ProductKey]), [Total Sales] )
VAR B = AVERAGEX ( VALUES('Sales'[ProductKey]), [Total Sales] )

Preferred — materialize once:

VAR Base = SUMMARIZECOLUMNS ( 'Sales'[ProductKey], "@TotalSales", [Total Sales] )
VAR A = SUMX ( Base, [@TotalSales] )
VAR B = AVERAGEX ( Base, [@TotalSales] )

Anti-pattern — context-independent expression inside iterator:

SUMX( 'Sales', 'Sales'[Quantity] * [Average Price] * 1.1 )
// [Average Price] doesn't change per row

Preferred:

VAR _AvgPrice = [Average Price]
RETURN SUMX( 'Sales', 'Sales'[Quantity] * _AvgPrice * 1.1 )

---

DAX004: Remove Redundant Filters

Identifier: repeated predicates, duplicate filter tables, or redundant key-set filters.

Action: Keep one copy of each filter. Remove key sets or variables that restate an active predicate.

Anti-pattern — same predicate in CALCULATE + FILTER:

CALCULATE(
    SUM('Sales'[Amount]),
    'Sales'[Year] = 2023,
    FILTER('Sales', 'Sales'[Year] = 2023)
)

Anti-pattern — redundant filter variable:

VAR FilteredValues = CALCULATETABLE ( DISTINCT ( 'Table'[Key1] ), 'Table'[Amount] > 1000 )
VAR Result =
    CALCULATETABLE (
        SUMMARIZECOLUMNS ( 'Table'[Key2], "TotalQty", SUM ( 'Table'[Quantity] ) ),
        'Table'[Amount] > 1000,
        'Table'[Key1] IN FilteredValues  -- redundant: already filtered by Amount > 1000
    )

Preferred — single filter, no duplication:

CALCULATE( SUM('Sales'[Amount]), 'Sales'[Year] = 2023 )

VAR Result =
    CALCULATETABLE (
        SUMMARIZECOLUMNS ( 'Table'[Key2], "TotalQty", SUM ( 'Table'[Quantity] ) ),
        'Table'[Amount] > 1000
    )

---

DAX005: Move Complex SUMMARIZE Inputs to CALCULATETABLE

Identifier: SUMMARIZE starts from a filtered or computed table expression.

Action: Keep grouping simple; move filters to an outer CALCULATETABLE.

Anti-pattern:

SUMMARIZE(
    CALCULATETABLE('Sales', 'Sales'[Year] = 2023, 'Customer'[Segment] = "Enterprise"),
    'Sales'[CustomerKey],
    "DistinctStores", DISTINCTCOUNT('Sales'[StoreKey])
)

Preferred:

CALCULATETABLE(
    SUMMARIZECOLUMNS(
        'Sales'[CustomerKey],
        "DistinctStores", DISTINCTCOUNT('Sales'[StoreKey])
    ),
    'Sales'[Year] = 2023,
    'Customer'[Segment] = "Enterprise"
)

---

DAX006: Precompute Iterator Inputs with SUMMARIZECOLUMNS

Identifier: iterator over VALUES(...) with CALCULATE or measure evaluation per row.

Action: Precompute iterator rows and values with SUMMARIZECOLUMNS, then iterate the materialized result.

Anti-pattern:

SUMX(
    VALUES('Product'[Attribute]),
    CALCULATE(SUM('Sales'[Amount]))
)

Preferred:

SUMX(
    SUMMARIZECOLUMNS(
        'Product'[Attribute],
        "@Amount", SUM('Sales'[Amount])
    ),
    [@Amount]
)

---

DAX007: Convert Boolean Tests Without IF

Identifier: row-by-row IF/SWITCH boolean conversion. Trace may show CallbackDataID when it cannot fold.

Action: Convert boolean-to-1/0 tests without IF; use a predicate + COUNTROWS when counting rows.

Anti-pattern:

SUMX(
    'Products',
    IF([Sales Amount] > 10000000, 1, 0)
)

Preferred:

SUMX(
    'Products',
    INT([Sales Amount] > 10000000)
)

Count rows case: eliminate the iterator and callback with a simple predicate.

-- Anti-pattern: iterator + conditional = callback
SUMX( 'Sales', IF('Sales'[Amount] > 1000, 1, 0) )

-- Preferred: native SE aggregation, no iterator, no callback
CALCULATE( COUNTROWS('Sales'), 'Sales'[Amount] > 1000 )

---

DAX008: Context Transition in Iterator

Identifier: measure reference or CALCULATE inside an iterator. Verify with FE time or repeated short SE events.

Action: Reduce or remove iterator context transitions:

1. Remove it completely:

// Instead of: SUMX( 'Sales', [Sales Amount] )
// Use: SUMX( 'Sales', 'Sales'[Unit Price] * 'Sales'[Quantity] )

2. Iterate over a narrow key table:

// Instead of: SUMX( 'Customer', [Total Sales] )
// Use: SUMX( VALUES('Customer'[CustomerKey]), [Total Sales] )

3. Reduce cardinality before iteration:

// Instead of: SUMX( 'Customer', [Total Sales] * 'Customer'[DiscountRate] )
// Use only when grouping customers by DiscountRate preserves the result:
SUMX(
    VALUES('Customer'[DiscountRate]),
    [Total Sales] * 'Customer'[DiscountRate]
)

---

DAX009: Externalize SUMMARIZECOLUMNS Filters

Identifier: filter arguments inside SUMMARIZECOLUMNS. Verify materialization and result shape.

Action: Move filters to a wrapping CALCULATETABLE.

Anti-pattern:

SUMMARIZECOLUMNS (
    'Table'[Column],
    TREATAS ( { "Value" }, 'Table'[FilterColumn] ),
    "@Calculation", [Measure]
)

Preferred:

CALCULATETABLE (
    SUMMARIZECOLUMNS (
        'Table'[Column],
        "@Calculation", [Measure]
    ),
    'Table'[FilterColumn] = "Value"
)

---

DAX010: Push Table Filters with CALCULATETABLE

Identifier: standalone FILTER(...) table where filter context could be applied directly. Simple cases can fold to WHERE.

Action: Use CALCULATETABLE so the filter applies before the table is consumed.

Anti-pattern:

FILTER( 'Sales', 'Sales'[Year] = 2023 )

Preferred:

CALCULATETABLE( 'Sales', 'Sales'[Year] = 2023 )

---

DAX011: Test DISTINCTCOUNT Alternatives

Identifier: DISTINCTCOUNT; trace cue is DCOUNT, especially when cells have different filter sets.

Action: Benchmark DISTINCTCOUNT against SUMX(DISTINCT(), 1); keep only if equivalent and faster for the target visual.

Storage Engine Bound:

DISTINCTCOUNT('Sales'[CustomerKey])

Formula Engine Bound (sometimes faster):

SUMX(DISTINCT('Sales'[CustomerKey]), 1)

---

DAX012: Preserve Filters Deliberately

Identifier: ALLEXCEPT or REMOVEFILTERS/ALL + VALUES used to preserve filters. Validate across report groupings.

Action: Choose the preservation form that matches how the retained value is filtered.

Use `ALLEXCEPT` only when the preserved column is already directly filtered:

CALCULATE( [Total Sales], ALLEXCEPT('Sales', 'Sales'[Region]) )

Keep `ALL/REMOVEFILTERS + VALUES` when the preserved value may be cross-filtered by another column:

CALCULATE( [Total Sales], REMOVEFILTERS('Sales'), VALUES('Sales'[Region]) )

---

DAX013: Keep Branch Measures SE-Friendly

Identifier: SWITCH/IF chooses between measure branches. Verify FE time and unused branch work in trace/query plan.

Action checklist:

• Read the selector column directly filtered by the slicer/query; avoid hidden sort keys unless filtered directly.
• Keep branches SE-native: one simple aggregation or one fact iterator with row-level arithmetic, not multiple separate aggregations.
• Use one numeric type across branches; cast mixed branches explicitly.
• Avoid iterator context transition; lift row-independent measures into variables.

For disconnected parameter tables, prefer field parameters or aligning the selector/filter column before making model metadata changes.

---

DAX014: Use COUNTROWS for Recognized Keys

Identifier: DISTINCTCOUNT over a recognized unique key. Trace may compile to COUNT; verify before rewriting.

Action: Prefer COUNTROWS only when the counted column is a recognized unique key.

• Safe candidates: table key, or one-side key of a regular active relationship.
• Test first: inactive relationships, USERELATIONSHIP, or unmarked keys.
• Non-key/high-cardinality bottleneck → see DAX011.

---

DAX015: Iterate at the Required Grain

Identifier: iterator scans more rows than a repeated measure/context-transition dependency requires. Do not use for simple SE-native column sums.

Action: Iterate the lowest distinct grain only when it removes repeated measure or context-transition work.

Anti-pattern:

-- 100K customers but only 5 distinct DiscountRate values → 100K context transitions
SUMX( 'Customer', CALCULATE(SUM('Sales'[Amount])) * 'Customer'[DiscountRate] )

Preferred:

-- 5 iterations instead of 100K
SUMX( VALUES('Customer'[DiscountRate]), CALCULATE(SUM('Sales'[Amount])) * 'Customer'[DiscountRate] )

---

DAX016: Test Relationship Overrides Locally

Identifier: bidirectional/M2M filter path, or TREATAS/CROSSFILTER override. Verify joins in xmSQL.

Action: Test alternate filter propagation in the measure with TREATAS/CROSSFILTER before changing the model.

Example — replace bidirectional bridge with explicit filter:

CALCULATE(
    SUM('Sales'[Amount]),
    CROSSFILTER('Customer'[CustomerKey], 'CustomerBridge'[CustomerKey], NONE),
    TREATAS(VALUES('CustomerBridge'[CustomerKey]), 'Customer'[CustomerKey])
)

---

DAX017: Align Scan Shape with Boolean Multipliers

Identifier: sibling measures differ only by per-measure filters on the same fact. Trace may show near-identical SE scans differing only by WHERE values.

Action: Move the per-measure filter into a boolean multiplier so competing SE scans can share shape; verify fusion in trace.

-- Anti-pattern: separate SE query per measure
CALCULATE( SUM('Sales'[Amount]), 'Product'[Category] = "Bikes" )
CALCULATE( SUM('Sales'[Amount]), 'Date'[Date] = _dateAnchor )
CALCULATE( MAX('Sales'[DateKey]),  'Sales'[Metric] <> 0 )

-- Fix: boolean multiplier — structurally similar SE queries can fuse; verify in trace
SUMX( KEEPFILTERS(ALL('Product'[Category])), CALCULATE(SUM('Sales'[Amount])) * ('Product'[Category] = "Bikes") )
SUMX( KEEPFILTERS(ALL('Date'[Date])),        CALCULATE(SUM('Sales'[Amount])) * ('Date'[Date] = _dateAnchor) )
MAXX( ALL('Date'[Date]),                     CALCULATE(MAX('Sales'[DateKey])) * INT(NOT ISBLANK(CALCULATE(SUM('Sales'[Metric])))) )

• KEEPFILTERS preserves external context; column in the groupby → detail cells iterate 1 row.
• Best for additive (SUM-style) aggregations.
• Validate MIN/MAX/AVERAGE: injected 0 values can corrupt the result.
• BLANK caveat: returns 0 instead of BLANK when no data exists; wrap if downstream ISBLANK() checks matter.

---

DAX018: Keep Iterator Division SE-Native

Identifier: DIVIDE() inside an iterator. Trace may show CallbackDataID when it cannot fold.

Action: Use / only when the denominator is known non-zero; otherwise pre-filter zero denominators first.

Anti-pattern:

SUMX('Fact', 'Fact'[BaseAmount] * DIVIDE(RELATED('Items'[Discount]), RELATED('Items'[LocationAdjustment])))

Preferred:

SUMX('Fact', 'Fact'[BaseAmount] * (RELATED('Items'[Discount]) / RELATED('Items'[LocationAdjustment])))

---

DAX019: Move Time Windows Outside Sibling Measures

Identifier: sibling measures each apply time-window filters. Verify whether trace shows separate SE scans.

Action: Keep base measures time-window free; apply TI once in the outer CALCULATE.

Custom time predicates: CALCULATE(expr, Column = _var) does not match this rule; see DAX017.

Anti-pattern — each measure applies TI independently (no fusion):

MEASURE 'Sales'[Revenue YTD] = CALCULATE ( [Revenue], DATESYTD('Date'[Date]) )
MEASURE 'Sales'[Cost YTD]    = CALCULATE ( [Cost],   DATESYTD('Date'[Date]) )
MEASURE 'Sales'[Margin YTD] =
    [Revenue YTD] - [Cost YTD]

Preferred — base measures fuse, TI applied once:

MEASURE 'Sales'[Margin YTD] =
    CALCULATE ( [Revenue] - [Cost], DATESYTD ( 'Date'[Date] ) )

---

DAX020: Keep Slice Measures Fusion-Friendly

Identifier: slice measures differ only by simple filters or dynamic values. Verify whether trace fuses or separates scans.

Action: Keep slice measures simple and literal; lift time-intelligence or dynamic filters to the combining measure.

Anti-pattern — TI inside each slice measure (no fusion):

MEASURE 'Sales'[Bikes YTD]       = CALCULATE ( SUM('Sales'[Amount]), 'Product'[Category] = "Bikes",       DATESYTD('Date'[Date]) )
MEASURE 'Sales'[Accessories YTD] = CALCULATE ( SUM('Sales'[Amount]), 'Product'[Category] = "Accessories", DATESYTD('Date'[Date]) )

Preferred — slice measures fuse, TI applied once:

MEASURE 'Sales'[Bikes]       = CALCULATE ( SUM('Sales'[Amount]), 'Product'[Category] = "Bikes" )
MEASURE 'Sales'[Accessories] = CALCULATE ( SUM('Sales'[Amount]), 'Product'[Category] = "Accessories" )
MEASURE 'Sales'[Combined YTD] = CALCULATE ( [Bikes] + [Accessories], DATESYTD('Date'[Date]) )

• Variable-driven slicers: leave base measures filter-free; put the dynamic predicate on the outer measure.
• Sliced column not in the groupby → see DAX017.

---

DAX021: Join Precomputed Key Sets in FE

Identifier: computed key set re-filters the same fact via TREATAS/IN. Trace cue is large IN/INB; ININDEX or compound tuples can also appear.

Action: Precompute both aggregations at the shared key grain, then join in FE.

• Avoid pushing computed key sets back to the fact scan with TREATAS/IN.
• Both sides must keep a shared lineage column for NATURALINNERJOIN.

Anti-pattern — TREATAS pushes key set back to SE, compounded by outer groupby:

VAR _FilteredAgg =
    CALCULATETABLE (
        ADDCOLUMNS ( VALUES ( 'Fact'[Key] ), "@Agg1", [Measure] ),
        'Dim'[Filter] = "X"
    )
VAR _Qualifying = FILTER ( _FilteredAgg, [@Agg1] > 1000000 )
VAR _Result =
    CALCULATE (
        [Measure],
        TREATAS ( SELECTCOLUMNS ( _Qualifying, "K", 'Fact'[Key] ), 'Fact'[Key] )
    )

Preferred — both aggregations pre-computed, joined in FE:

VAR _FilteredAgg =
    CALCULATETABLE (
        ADDCOLUMNS ( VALUES ( 'Fact'[Key] ), "@Agg1", [Measure] ),
        'Dim'[Filter] = "X"
    )
VAR _Qualifying = FILTER ( _FilteredAgg, [@Agg1] > 1000000 )
VAR _UnfilteredAgg =
    ADDCOLUMNS ( VALUES ( 'Fact'[Key] ), "@Agg2", [Measure] )
VAR _Joined = NATURALINNERJOIN ( _Qualifying, _UnfilteredAgg )
VAR _Result = SUMX ( _Joined, [@Agg2] )

Goal: replace the final key-set fact scan with precomputed tables and an FE join. Keep only if trace/runtime improves.

---

Section 4: Tier 2 Query Structure Patterns

STOP — Requires user approval before applying any change. Explain the impact on query output and wait for explicit confirmation.

Scope: Desktop-Achievable Changes Only

>

Every Tier 2 recommendation must map to an action the report author can perform in Power BI Desktop's UI. The agent optimizes the generated DAX query, but the user implements changes through the Desktop interface — not by editing DAX directly in the query pane. Examples of valid changes:

- Changing the axis/groupby field (e.g., swap Calendar Date for Calendar Month on a visual axis)

- Removing or adding visual-level filters (e.g., drop an unneeded slicer selection)

- Changing filter values (e.g., narrow a date range filter)

- Removing measure value filters (e.g., remove a "Top N" or "> threshold" filter from a visual)

- Changing aggregation type on a column (e.g., Sum → Average)

QRY001: Remove Unneeded Filters

Every filter adds a WHERE clause in xmSQL and may force an extra SE join. Users often apply slicer or visual-level filters that don't affect the calculation being optimized.

Detection: WHERE clauses on columns not used in the measure logic, or filter variables that restrict to a single value (e.g., Currency[Code] = "USD" in a USD-only model).

Fix: Remove filters one at a time and re-run; if the result doesn't change, the filter is unneeded. Filters needed across all visuals → push to the data source (model-level — see Section 5).

-- Before: filter on Currency adds an SE join for no benefit
SUMMARIZECOLUMNS (
    'Product'[Category],
    KEEPFILTERS ( TREATAS ( {"USD"}, 'Currency'[Code] ) ),
    "Revenue", [Total Revenue]
)

-- After: filter removed, same result, one fewer SE join
SUMMARIZECOLUMNS ( 'Product'[Category], "Revenue", [Total Revenue] )

---

QRY002: Eliminate Report Measure Filters (__ValueFilterDM)

When a visual filters on a measure value (e.g., "Revenue > 1M"), Power BI generates a __ValueFilterDM variable that can evaluate the measure twice — once for the filter check, once for display.

Detection: __ValueFilterDM in the generated query.

Fix: Move the threshold into the measure itself — return BLANK below the cutoff. SUMMARIZECOLUMNS auto-drops blank rows, achieving the same visual result in one pass:

MEASURE 'Sales'[Total Revenue Filtered] =
    VAR __Rev = [Total Revenue]
    RETURN IF ( __Rev > 1000000, __Rev )

---

QRY003: Reduce Query Grain

Grouping by a high-cardinality column (e.g., Calendar[Date] → 365 rows) when the user only needs monthly data (12 rows) inflates SE row count ~30×.

Detection: Groupby on a date or high-cardinality column producing far more rows than the visual needs.

Option A — coarser groupby:

-- Daily → monthly
SUMMARIZECOLUMNS ( 'Calendar'[YearMonth], "Revenue", [Total Revenue] )

Option B — period-end axis + measure pin (show period-end snapshot instead of full-period aggregate):

Requires a period-end column in the date table (e.g., Calendar[MonthEndDate]). User changes the visual axis to it, then pins the measure to that date:

-- User changes axis from Calendar[Date] to Calendar[MonthEndDate]
-- Measure pins CALCULATE to the period-end date to return that day's value only
MEASURE 'Sales'[Active Customers] =
    CALCULATE (
        DISTINCTCOUNT ( 'Sales'[CustomerID] ),
        'Calendar'[Date] = MAX ( 'Calendar'[MonthEndDate] )
    )

Without the pin, grouping by MonthEndDate aggregates all days in the month instead of returning the single-day value.

Option C — return BLANK for non-boundary dates (keeps all dates in groupby but only computes on end-of-month):

MEASURE 'Sales'[Revenue EOM] =
    IF ( MAX('Calendar'[Date]) = EOMONTH(MAX('Calendar'[Date]), 0), [Total Revenue] )

Option D — daily additive measure approximated at coarser grain (divide monthly total by days in month):

MEASURE 'Sales'[Daily Avg Revenue] =
    DIVIDE (
        [Total Revenue],
        DAY ( EOMONTH ( MAX('Calendar'[Date]), 0 ) )
    )

---

QRY004: Remove BLANK Suppression (Changes Result Shape)

+ 0, IF(ISBLANK([M]), 0, [M]), or COALESCE(..., 0) force SUMMARIZECOLUMNS to evaluate every groupby combination — including rows with no data — inflating the result set.

Detection: + 0, IF(ISBLANK(...)), or COALESCE(..., 0) appended to measures.

Anti-pattern:

MEASURE 'Sales'[Revenue] = SUM ( 'Sales'[SalesAmount] ) + 0

Preferred:

MEASURE 'Sales'[Revenue] = SUM ( 'Sales'[SalesAmount] )

If zeros are required selectively, conditionally add 0 where it makes sense:

MEASURE 'Sales'[Revenue] =
    VAR _ForceZero = NOT ISEMPTY ( 'Sales' )
    RETURN [Sales Amount] + IF ( _ForceZero, 0 )

DAX Performance Optimization Guide

Complete framework for optimizing DAX query performance: tier model, phased workflow, decision guide, trace diagnostics, and on-demand pattern routing.

Reading Guide

Must Read — Every Optimization

Always read these sections fully before starting any optimization session:

• [Optimization Framework](#optimization-framework) — tiers, autonomy rules, tool requirements
• [Phase 1: Establish Baseline](#phase-1-establish-baseline) — measure resolution, model context, run protocol
• [Phase 2: Optimization Iterations](#phase-2-optimization-iterations) — apply, test, compare, iterate
• [Section 1: How the Engine Works](./engine-internals.md#section-1-how-the-engine-works) — FE/SE architecture, xmSQL, segments, fusion
• [Section 2: Trace Diagnostics](./engine-internals.md#section-2-reading-and-diagnosing-traces) — metrics, event waterfall, signal interpretation

Consult When Needed

Read these only when directed by the Decision Guide or after Tier 1 is exhausted:

• [Section 3: Tier 1 — DAX Patterns](./dax-patterns.md#section-3-tier-1-dax-optimization-patterns) — DAX001–DAX021 — load only routed candidate patterns first
• [Section 4: Tier 2 — Query Structure](./dax-patterns.md#section-4-tier-2-query-structure-patterns) — QRY001–QRY004 — requires user approval before applying
• [Section 5: Tier 3 — Model Changes](./model-optimization.md#section-5-tier-3-model-optimization-patterns) — MDL001–MDL009 — high caution, user approval, suggest model copy
• [Section 6: Tier 4 — Direct Lake](./model-optimization.md#section-6-tier-4-direct-lake-optimization-patterns) — DL001–DL002 — high caution, user approval, requires ETL/pipeline changes

---

Decision Guide

Use this table as a router into Section 3. Route by trace evidence when available; otherwise route by DAX shape and treat the match as a hypothesis until execution results confirm it. Load only the routed candidate patterns first; read the rest of Section 3 only if no signal matches or routed patterns are exhausted. Sections 4–6 signals are escalation triggers; consult those sections only when the signal appears.

Section 3 — Where to Start (read all of §3)

No signal matches? Read all of §3 — patterns DAX001–DAX021 cover the full range.

Sections 4–6 — Escalation Triggers

Only consult these sections if the corresponding signal is present. All require user approval before applying changes.

---

Optimization Framework

Tiers and Autonomy

Success criteria — Tier 1: Query duration improvement AND semantic equivalence (same row count, column count, data values). Success criteria — Tier 2/3/4: Query duration improvement AND explicit user approval of output or structural changes.

Requirements

• Semantic model connection — Any client that satisfies the Trace capture and Model metadata requirements below. See Trace Capture Methods for capability comparison across common clients.
• Trace capture — Ability to execute DAX queries with server timing trace capture. See Trace Capture Methods.
• Model metadata — Ability to read measure definitions, function definitions, calculation group expressions, table metadata, and relationship metadata from the model.

Trace Capture Methods

---

Phase 1: Establish Baseline

Step 1: Resolve All Measure and Function Definitions

Before optimizing, fully resolve every DAX expression in the query. Partial visibility leads to incorrect or incomplete optimizations.

1. Identify measure references in the user's query — any [MeasureName] pattern. 2. Retrieve each measure's expression — read the measure definition (name, table, DAX expression) from the model. 3. Recursively resolve dependencies — read each expression, find nested [OtherMeasure] calls, fetch those too. 4. Retrieve user-defined functions if referenced. 5. Build a DEFINE block that explicitly inlines all resolved measures and functions. 6. Check for active calculation groups — list all calculation groups in the model, retrieve their calculation item expressions. Note any that may be active in the query context as they affect query plans for every intercepted measure.

Example: If [Profit Margin] = DIVIDE([Total Profit], [Total Revenue]), retrieve all three definitions and build:

DEFINE
    MEASURE 'Sales'[Total Revenue] = SUM('Sales'[Revenue])
    MEASURE 'Sales'[Total Profit]  = SUM('Sales'[Revenue]) - SUM('Sales'[Cost])
    MEASURE 'Sales'[Profit Margin] = DIVIDE([Total Profit], [Total Revenue])

EVALUATE
SUMMARIZECOLUMNS ( 'Product'[Category], "Profit Margin", [Profit Margin] )

Step 2: Gather Model Context

1. List all tables — understand table structure and storage modes (Import, DirectQuery, Direct Lake). 2. List all relationships — understand join paths and filter propagation.

This context helps distinguish model design issues (missing star schema, bidirectional relationships) from DAX expression problems.

Step 3: Execute Baseline (1 warm-up + 3 measured runs)

For each run:

1. Clear VertiPaq cache — clears the SE query cache only; columns stay resident.

• Warm-up run: cold (on disk) → warm (resident).
• Measured runs: warm + no-cache — the ideal optimization-test state.

2. Execute with trace capture — run the DAX query with server timing trace enabled. 3. Derive key metrics — Total Duration, FE/SE split, SE query count, peak memory, and result row count. See Understanding FE vs. SE Metrics for derivation from trace events. 4. Record all metrics, save the full trace events, and save the baseline result data for semantic equivalence checks.

After all runs: discard warm-up, take the median of the 3 measured runs as the baseline. If results are inconsistent (>20% spread), run up to 5 more iterations to isolate platform noise from actual query performance. Record the baseline's full metrics, trace events, and CSV result.

Isolating measures: When a query has many measures and the trace is noisy, comment out all but one (or a small group), re-run, and compare. Repeat in groups to isolate which measures drive the majority of total duration.

Step 4: Analyze Baseline

Apply [Section 2: Trace Diagnostics](./engine-internals.md#section-2-reading-and-diagnosing-traces) to interpret the metrics and events. Use the Decision Guide above to identify which Section 3 patterns to try first.

---

Phase 2: Optimization Iterations

Step 1: Select and Apply Optimizations

Using Section 3 (Tier 1), start from trace identifiers when available; otherwise use the DAX-only fallback patterns as hypotheses. Apply one or more of DAX001–DAX021.

CRITICAL: Modify only the measure definitions in the DEFINE block. Do NOT change the EVALUATE clause or SUMMARIZECOLUMNS grouping columns. Query structure must stay identical to preserve semantic equivalence.

Step 2: Execute and Compare

1. Clear the VertiPaq cache (returns the model to the warm + no-cache state — same condition as the baseline measured runs). 2. Execute the query with trace capture enabled.

During iteration: 1 run is sufficient — columns are already resident from baseline, so no warm-up is needed; clearing only the SE cache keeps the warm + no-cache state. Reserve the full protocol (1 warm-up + 3 measured, take median) for the final confirmation against the original baseline.

Evaluate:

• Improvement = (BaselineDuration − OptimizedDuration) / BaselineDuration × 100
• Semantic equivalence: Compare the CSV result from this run against the baseline CSV — same row count, same columns, same data values. If results differ, the change modified calculation semantics — revert it. Check this immediately after each iteration, not after multiple changes.

Step 3: Iterate and Escalate

• Meaningful improvement + semantically equivalent → Success. "Meaningful" = exceeds the baseline's run-to-run noise band (e.g., baseline 1200/1280/1310 ms → 1200 ms is noise; 900 ms is real). Present to user; offer the optimized query as new baseline for further rounds (compound improvements are common).
• Further rounds: Re-run Phase 1 Steps 3–4 on the new baseline; re-analyze the new structure against the Decision Guide, as it may expose patterns that didn't apply before (fusion, materialization, etc.).
• Within the noise band → Try another Section 3 pattern, or combine patterns. Re-examine trace for other bottlenecks.
• Results differ → Revert; the optimization changed semantics. Try another approach.
• Tier 1 exhausted → Move to Phase 3 (Tier 2) with user approval. "Exhausted" = every signal-matching pattern tried (individually + combined), measures isolated for multi-measure queries, last 1–2 attempts at noise floor.

---

Phase 3: Query Structure Changes (Tier 2 — User Approval Required)

STOP — Do not modify the query structure without explicit user approval.

Consult [Section 4: Tier 2 — Query Structure Patterns](./dax-patterns.md#section-4-tier-2-query-structure-patterns) (QRY001–QRY004).

Before applying any change:

1. Explain the specific change (e.g., "Group by YearMonth instead of Date reduces result rows from 365K to 12K"). 2. Explain what changes in the output and what the user gains in performance. 3. Wait for explicit approval. 4. If approved, modify query structure, run the full baseline cycle, present results.

---

Phase 4: Model and Data Layout Changes (Tier 3/4 — High Caution, User Approval Required)

STOP — Do not modify the model without explicit user approval.

Consult [Section 5: Tier 3 — Model Patterns](./model-optimization.md#section-5-tier-3-model-optimization-patterns) (MDL001–MDL009) and [Section 6: Tier 4 — Direct Lake](./model-optimization.md#section-6-tier-4-direct-lake-optimization-patterns) (DL001–DL002).

Before proceeding:

1. Present the specific diagnosis and proposed model change. 2. Explain why the model design is causing the performance bottleneck. 3. Warn that model changes can break downstream reports and visuals. 4. Suggest creating a copy of the semantic model to experiment on. 5. Identify if upstream changes are required (Lakehouse tables, Warehouse views, Power Query transformations) — these cannot be done through semantic model tooling alone. 6. If approved, coordinate with the user's CI/CD process. 7. After applying changes, re-run the full baseline optimization workflow to measure impact.

---

Error Handling

• Connection failure — Verify dataset name, workspace name, or XMLA endpoint. For Desktop, ensure Power BI Desktop is running and note the local port. For Service, verify XMLA read/write is enabled on the capacity.
• Query syntax error — Validate DAX syntax before executing.
• Semantic equivalence failure — Optimization changed calculation semantics. Review filter context, aggregation granularity, and CALCULATE filter arguments. Revert and try differently.
• No improvement found — Some queries are already well-optimized at the DAX level. Check whether the bottleneck is data layout (Phase 4) or query structure (Phase 3).
• Trace events empty — Ensure server timing / trace capture is enabled before executing the query. Verify the trace is subscribed to the correct event types (QueryEnd, VertiPaqSEQueryEnd, VertiPaqSEQueryCacheMatch).

---

Reference Files

The detailed reference material is split into focused files for progressive disclosure:

• [Engine Internals](./engine-internals.md) — FE/SE architecture, xmSQL, compression/segments, SE fusion, trace diagnostics (Sections 1-2)
• [DAX and Query Structure Patterns](./dax-patterns.md) — Tier 1 DAX patterns DAX001-DAX021, Tier 2 query structure QRY001-QRY004 (Sections 3-4)
• [Model and Direct Lake Optimization](./model-optimization.md) — Tier 3 model patterns MDL001-MDL009, Tier 4 Direct Lake DL001-DL002 (Sections 5-6)

Engine Internals

How the DAX engine works: Formula Engine (FE) vs. Storage Engine (SE) architecture, xmSQL query language, compression and segments, SE query fusion, and trace diagnostics.

Related references: DAX and Query Structure Patterns · Model and Direct Lake Optimization

---

Section 1: How the Engine Works

Query Processing Architecture

Every DAX query runs through two components: the Formula Engine (FE) and the Storage Engine (SE).

The FE handles all DAX — branching logic, context transitions, complex arithmetic, measure evaluation. It is single-threaded and the bottleneck in most poorly written queries.

The SE reads compressed columnar data from VertiPaq. It is multi-threaded and very fast, but supports only a limited set of operations: the four basic arithmetic operators, GROUP BY, LEFT OUTER JOINs, and basic aggregations (SUM, COUNT, MIN, MAX, DISTINCTCOUNT).

For DirectQuery models, the data source serves as the SE role: the FE generates and pushes down SQL, trading network/source latency for in-memory scan cost.

How they interact:

• FE requests data via one or more SE scans; each result is a datacache (columns + aggregated values).
• Complex queries need multiple datacaches (e.g., one builds a filter set, one aggregates the fact).
• If the SE can't evaluate an expression natively → callback to FE row-by-row → that scan is effectively single-threaded.

The core principle of DAX optimization: push as much work as possible into the SE, minimize SE scans, and eliminate callbacks entirely.

---

xmSQL: The Storage Engine Query Language

xmSQL is the human-readable representation of SE scan activity in trace events — it shows which tables are scanned, which columns are aggregated, which filters apply, and how joins resolve. Syntax resembles SQL with key differences:

Implicit GROUP BY: Every column in the SELECT list is automatically a grouping column — no GROUP BY keyword.

Computed expressions: Row-level calculations use a WITH block with :=, referenced in aggregations via @:

WITH $Expr0 := ( 'Sales'[UnitPrice] * 'Sales'[OrderQuantity] )
SELECT Product[Category], SUM ( @$Expr0 )
FROM Sales
    LEFT OUTER JOIN Product ON 'Sales'[ProductKey] = Product[ProductKey]

Relationship joins are LEFT OUTER unless otherwise stated: the many-side table is FROM, the one-side is joined in. Other internal forms (INNER JOIN, REDUCED BY, reverse joins) can also appear depending on the operation.

Semi-join projections: Appear as DEFINE TABLE $Filter0 ... ININDEX in xmSQL — an initial dimension scan builds a key index injected into the fact WHERE clause.

Callbacks: Occur whenever the SE must compute an expression that falls outside of VertiPaq's native capabilities. Forms include CallbackDataID (arbitrary expressions, most common) and specialized variants exposing individual FE functions (rounding, log/abs math). See DAX002, DAX007, DAX008, DAX018 for callback elimination patterns.

---

Compression, Segments, and Parallelism

Compression determines scan speed. VertiPaq uses dictionary and run-length-style encodings to reduce scan work. For Direct Lake, source Delta/Parquet layout affects how quickly columns load into VertiPaq; V-Order improves RLE-friendly layout for read-heavy Power BI tables (see DL001).

Segments are fixed-size column chunks — the unit of both compression and parallel execution. The SE assigns one CPU thread per segment, so segment count determines how many cores a scan can utilize.

Parallelism: A 32M-row table in 2 segments uses 2 threads; in 32 segments it uses all 16 available threads — a 4–8× speedup with zero DAX changes.

Segment skew matters equally: if one segment has 15M rows and the rest have 1M, the scan bottlenecks on the oversized segment. Segments must be evenly sized for parallelism to be effective.

Diagnosing low parallelism: The SE Parallelism Factor (StorageEngineCpuTime ÷ StorageEngineDuration) shows thread utilization. Values near 1.0 mean single-threaded execution; values of 8–16 indicate strong multi-core use. When a trace shows few SE queries (1–4), high SE Duration, Parallelism Factor ≈ 1.0, and clean xmSQL — the bottleneck is likely too few segments or skewed segment sizes. DAX changes are unlikely to help; use data layout instead (see General Data Layout Best Practices and DL001–DL002).

---

SE Query Fusion

Fusion is the engine's ability to combine multiple SE scans into fewer scans. Two flavors:

• Vertical fusion merges multiple measure aggregations that share the same filter context into a single SE query. Three measures on the same fact table under the same filter = one scan instead of three. Gain scales with fact table size.
• Horizontal fusion merges SE queries that differ only in which value(s) of a column they filter. N separate fact scans collapse to one; the FE partitions the result.

Why fusion breaks. Fusion needs the competing SE requests to have compatible scan shapes — the same filter context for vertical fusion, compatible single-column filter differences for horizontal. Common triggers that break that:

• FE chooses the branch — SWITCH/IF between measures, or per-measure filter predicates that materialize separate VAND tuples → structurally different SE queries → see DAX017
• Table- or range-valued filter — time intelligence (DATESYTD, DATEADD, etc.) injects a per-measure date/range scan the SE can't fold in → see DAX019
• Slicing column not in the groupby — horizontal fusion can only merge slices the result groups by; absent that, scans stay separate
• Runtime-computed filter value — a predicate held in a variable is treated as dynamic and won't fuse
• Calculation group items — each item applies its own filter modification → structurally different SE query

Trace diagnosis: Same fact table + same joins across multiple SE queries → missed vertical fusion. Near-identical queries differing only by WHERE values → blocked horizontal fusion. See Section 3 and Section 2 trace analysis.

---

Section 2: Reading and Diagnosing Traces

Understanding Formula Engine (FE) vs. Storage Engine (SE) Metrics

These are the critical metrics for DAX optimization, derived from Analysis Services trace events.

Net wall-clock: SE Duration is the union of overlapping SE intervals — not the sum of individual durations. Three concurrent 100ms scans = ~100ms wall clock, not 300ms.

Parallelism — aggregate vs. per-scan: The aggregate parallelism factor is computed across all SE scans. Each individual scan has its own CpuTime / Duration. A healthy aggregate factor can mask a single unparallelized scan where CpuTime ≈ Duration.

FE processing gaps: FE Duration is the sum of all time intervals where no SE query was executing — gaps between SE events on the timeline.

Analyzing Trace Events

Trace events are captured from the Analysis Services engine during query execution. Each event includes: EventClass (event type), EventSubclass, TextData (xmSQL or DAX), Duration, CpuTime, StartTime, EndTime.

Key event types:

• VertiPaqSEQueryBegin / VertiPaqSEQueryEnd — SE scan lifecycle. Duration and CpuTime are on the End event. TextData contains the xmSQL query.
• VertiPaqSEQueryCacheMatch — SE query answered from cache (no scan). Count these separately.
• QueryBegin / QueryEnd — Overall DAX query lifecycle. Duration on QueryEnd = total wall-clock time.
• AggregateTableRewriteQuery — Fired when the engine rewrites a query to use an aggregation table. TextData contains the rewritten query. Presence indicates the engine found and used an agg table hit — absence on an agg-enabled model means the query fell through to the detail table.

Filtering trace output: Focus on the event types above. Ignore VertiPaqScanInternal subclass events — these duplicate the outer VertiPaqScan with internal detail (e.g., DC_KIND="DENSE") and identical timing. Also ignore CommandBegin/CommandEnd (DAX execution wrapper, no diagnostic value) and Error events (only relevant when errors occur).

Per-scan derived metrics (from VertiPaqSEQueryEnd events):

Each VertiPaqSEQueryEnd event provides the raw data to derive per-scan diagnostics:

• Rows scanned / Marshalling KB — parse [Estimated size (volume, marshalling bytes): X, Y] at the end of TextData. X = rows, Y = bytes. Identifies excessive materializations on a specific scan.
• Per-scan parallelism — CpuTime / Duration for that individual scan. A ratio near 1.0 means single-threaded even if the aggregate storageEngineCpuFactor looks healthy.
• Callbacks on slow scans — scan TextData for CallbackDataID/EncodeCallback to confirm which specific SE query has the callback.

Building an FE gap waterfall:

FE processing occurs in the gaps between SE events. Use StartTime/EndTime offsets from QueryBegin.StartTime to build a timeline: 1. Gap between QueryBegin and the first SE StartTime → FE plan compilation 2. Gap between one SE EndTime and the next SE StartTime → FE processing block 3. Gap between the last SE EndTime and QueryEnd.EndTime → final FE assembly 4. Overlapping SE events → parallel SE execution; sequential non-overlapping → FE feeding results between scans 5. A large gap (>100ms) signals expensive FE computation — examine the SE query before the gap

What to Look For

Scan for these signals in priority order when analyzing a slow query:

1. Callbacks — CallbackDataID or EncodeCallback in SE TextData. Fix first (DAX002, DAX007, DAX008, DAX018). 2. High FE % — FE doing too much work; usually paired with many short SE queries. 3. High SE query count / repeated fact scans — multiple SE queries hitting the same fact table with same joins but different WHERE clauses or aggregations → blocked fusion. See SE Query Fusion. 4. Large materializations — SE rows far exceed final result, or SE queries with no WHERE clause → FE filtering post-materialization instead of pushing to SE. See DAX009. 5. Low parallelism factor — near 1.0 on slow scans → data layout problem, not DAX. See Compression, Segments, and Parallelism. 6. High KB per SE event — wide intermediate tables; reduce columns or aggregate earlier. 7. Two-step dimension pre-scans — dimension-only SELECT followed by where predicate on the fact. Restructure query to collapse into one scan. 8. Large semi-join index tables — DEFINE TABLE + ININDEX or WHERE ... IN with hundreds of compound tuples (e.g., (GroupByCol, FilterKey) pairs). See DAX021. 9. Missing aggregate table hit — Model has agg tables configured but no AggregateTableRewriteQuery event in the trace → query fell through to the detail table. Check agg table mappings and query grain.

Prioritization: Callbacks → Large FE processing → SE query count (DAX) → parallelism and data volume (data layout). Target the highest-duration SE scan first — ignore 0ms cache-hit scans.

---

DAX vs. Data Layout: Reading the Signal

Many SE queries + high FE time + individually short SE scans → DAX problem

Fusion is blocked, callbacks are present, or filters resolve iteratively. Fix the DAX — see Section 3 and Section 4. Example: 109 SE queries, 30% FE → after restructuring: 4 SE queries, 1% FE.

Few SE queries + low FE time + high SE duration + low parallelism → Data layout problem

The DAX is clean but SE scans are slow due to insufficient segments or poor compression. DAX changes will not help — see Section 5 / Section 6 (General Data Layout Best Practices, DL001–DL002).

---

Further Reading

• Analysis Services trace events
• Horizontal fusion in Power BI and Analysis Services
• Understand Direct Lake query performance

Model and Direct Lake Optimization

Tier 3 model patterns (MDL001-MDL009) and Tier 4 Direct Lake patterns (DL001-DL002).

Related references: Engine Internals · DAX and Query Structure Patterns

---

Section 5: Tier 3 Model Optimization Patterns

STOP — Requires user approval before applying any change. Warn that model changes can break downstream reports. Suggest working on a model copy. Apply changes through whatever semantic-model authoring path is already in use; upstream source changes (Lakehouse, Warehouse, Power Query) must be coordinated with the user's data engineering or pipeline workflow.

General Data Layout Best Practices

Data layout decisions affect performance at the source level — before DAX, before the SE. Apply after exhausting DAX and query structure optimizations; changes here require ETL or pipeline modifications. Apply to both Import and Direct Lake.

1. Remove unused columns and filter rows at the source. 2. Drop all-null/all-zero fact rows that never contribute to results. 3. Move low-cardinality string attributes off the fact table into dimensions with integer keys. 4. Partition on high-filter columns (DateKey, TenantKey) so the engine skips entire files. Use Z-order clustering when partitioning creates too many small files. 5. Presort on the most filtered/grouped column first (e.g., DateKey, then ProductKey). RLE compression improves dramatically when values cluster into longer runs per segment. 6. Use optimal data types. See MDL003.

---

MDL001: Many-to-Many Relationship Optimization

Bridge tables create expanded tables the engine materializes every query. The right layout depends on filter paths, bridge cardinality, and RLS. Test each option. Scenario: User (security), Customer (dimension), UserCustomer (bridge), Fact.

A — Canonical (bidir bridge): User 1──* UserCustomer *──bidir──1 Customer 1──* Fact Customer filters Fact directly; bridge only traversed for User. Best when User is rarely a slicer alongside Customer. Bidir causes high FE cost when both filter together.

B — M2M bridge to fact (no bidir):

User 1──* UserCustomer *──1 Customer
                │
                *──M2M──* Fact

Both dims always filter through bridge M2M. Best when consistent query times matter more than peak Customer-only performance.

C — Optimized hybrid: User 1──* UserCustomer *──M2M──* Fact *──1 Customer Customer filters Fact directly; User filters through bridge M2M. No bidir. Best general-purpose layout. Use inactive relationship + USERELATIONSHIP if you need Customer↔UserCustomer cross-queries.

D — Pre-computed combination key: User 1──* UserCombinations *──M2M──* Fact *──1 Customer ETL assigns a surrogate key per unique set of customers a user can access — users with identical access share one key. Best when bridge is very large or many users share the same access patterns.

---

MDL002: Star Schema Conformance

Snowflake schemas force multiple SE joins per query. Flatten dimension chains into a single wide dimension to reduce join depth and enable better fusion.

Sales ──* Product ──* Subcategory ──* Category → Sales ──* Product [ProductKey, ProductName, Subcategory, Category]

---

MDL003: Column Cardinality and Data Type Optimization

High-cardinality columns inflate dictionary size and segment memory.

• Integer keys over string keys: Replace "PROD-001234" with integer surrogates.
• Reduce timestamp precision: DateTime → Date when queries only group by date.
• Bin continuous values: 50K distinct decimals → binned ranges if measure logic allows.
• Split high-cardinality columns: FullAddress (100K distinct) → City, State, Zip.

---

MDL004: Aggregation Table Strategies

Pre-summarized Import tables intercept SE queries before they hit large DQ facts. Aggregate Awareness redirects automatically — no DAX changes.

Setup: GROUP BY [FKs], SUM([Metrics]) → load as Import → connect to same dimensions → map in Manage Aggregations as SUM OF [FactTable[Column]]. Fact tables must be DQ.

Filtered Aggs (hot/cold split): Import only recent data (e.g., last 3 months). 95%+ queries served from Import.

---

MDL005: Pre-Compute Period Comparison Columns

Period-over-period calcs (YoY, MoM) require two SE scans. Pre-computing prior-period values as physical columns on the fact row reduces it to one scan.

Before (two scans):

YoY = SUM ( 'Fact'[Sales] ) - CALCULATE ( SUM ( 'Fact'[Sales] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

After (one scan):

YoY = SUM ( 'Fact'[Sales] ) - SUM ( 'Fact'[SalesLY] )

Wider fact table, but eliminates the TI scan entirely. Best for fixed period comparisons on large DQ tables.

---

MDL006: Row-Based Time Intelligence Table

DAX TI functions break vertical fusion — each period measure gets its own SE query. A row-based TI table pre-materializes all periods as data rows so all period measures fuse into a single SE scan.

Table: Period (slicer label), Date (actual dates → relationship to fact), AxisDate (x-axis anchor). Relate via M2M to Fact or BiDir through Calendar.

---

MDL007: Eliminate Referential Integrity Violations

Fact FKs with no matching dimension row prevent inner-join rewriting for SWITCH/multi-measure patterns.

Detection:

SELECT [Dimension_Name], [RIVIOLATION_COUNT]
FROM $SYSTEM.DISCOVER_STORAGE_TABLES
WHERE [RIVIOLATION_COUNT] > 0

Fix: Add an "Unknown" catch-all row to the dimension and map missing foreign keys in fact to "Unknown" record.

---

MDL008: Replace SEARCH/FIND Filters with Pre-Computed Boolean Columns

SEARCH()/FIND() in filters forces row-by-row string scanning. Pre-compute the result as a boolean column (cardinality 2, ~1 bit/row) for pure columnar access. Generalizes to any fixed-value logical test — date flags, category indicators, prefix checks.

---

MDL009: Cardinality Reduction via Historical Value Substitution

Replace old key values beyond a retention window with a single placeholder to collapse cardinality and shrink dictionaries. This can be done in both facts and dimensions.

CASE WHEN SaleDate >= DATEADD(year, -1, GETDATE()) THEN SalesKey ELSE 'Historical Key' END

---

Section 6: Tier 4 Direct Lake Optimization Patterns

STOP — Requires user approval before applying any change. Changes here require Spark/ETL jobs or Fabric resource profile configuration outside the semantic model. Coordinate with the user's data engineering workflow.

Direct Lake reads OneLake Delta/Parquet files and loads columns into VertiPaq on demand. Its speed depends on source file layout, memory residency, and segment health.

DL001: V-Ordering Delta Tables for Direct Lake

Import refresh builds optimized VertiPaq storage. Direct Lake depends on Delta/Parquet layout at query time, so use V-Order for read-heavy Power BI tables to improve compression and column loading.

Two approaches:

• Spark: spark.conf.set("spark.sql.parquet.vorder.default", "true"), then run OPTIMIZE for existing Delta tables.
• Fabric resource profile: Use the `readHeavyForPBI` resource profile, which enables V-Order-oriented write settings for Power BI reads.

---

DL002: Segment Size and Parallelism

Parquet row groups shape VertiPaq column segment size/count (see SE Parallelism Factor in Section 1).

Target: 1–16M rows per rowgroup. Too few rowgroups → single-threaded scans; too many tiny rowgroups → merge overhead. For small tables (< 1M rows) this rarely matters. Run OPTIMIZE regularly to consolidate small files into properly sized rowgroups.