~12 min read

DAX Patterns

Time, Variables, and Beyond

Powerful patterns that turn DAX knowledge into real-world solutions.

DAX Fundamentals series
Scroll to explore

Building On Context

Everything you've learned so far leads here

You've built the foundation. Now it's time to put it to work.

Time Intelligence

Period comparisons: YTD, Prior Cycle, QTD

Variables

Cleaner, faster, more debuggable formulas

Relationships

Active, inactive, and virtual via USERELATIONSHIP

Calculation Groups

Eliminate duplication across your entire model

Time Intelligence

Comparing data across time periods: the most common reporting need

Prerequisite: A proper date table. Time intelligence functions need a table with contiguous dates, every single day, no gaps. Mark it as a date table in your model.

These functions return tables of dates. You pass them into CALCULATE, and CALCULATE uses those dates to override the current filter context.

Revenue YTD = CALCULATE( [Total Revenue], DATESYTD('Stardate'[Date]) )
Revenue Prior Cycle = CALCULATE( [Total Revenue], DATEADD('Stardate'[Date], -1, YEAR) )

Function Reference

DATESYTD( 'Stardate'[Date], "MM/DD" )

Returns all dates from January 1 (or your fiscal year start) through the last date in the current filter context. Resets on Jan 1 each year. Optional second parameter sets a custom fiscal year end date.

Example: On Mar 15, returns Jan 1 - Mar 15 (75 days).

DATESQTD( 'Stardate'[Date] )

Returns dates from the current quarter start through the last date in context. Resets at each quarter boundary (Q1=Jan, Q2=Apr, Q3=Jul, Q4=Oct).

Example: On Mar 15 (Q1), returns Jan 1 - Mar 15.

DATESMTD( 'Stardate'[Date] )

Returns dates from the 1st of the current month through the last date in context. Resets on the first of each month.

Example: On Mar 15, returns Mar 1 - Mar 15 (15 days).

DATEADD( 'Stardate'[Date], <intervals>, <YEAR|QUARTER|MONTH|DAY> )

Shifts the entire date filter by the specified interval. Use -1 YEAR for prior year, -1 QUARTER for prior quarter, etc. The most flexible time intelligence function.

Example: DATEADD(..., -1, YEAR) on Mar 15, 2226 returns Mar 15, 2225.

Interactive Stardate

Click any date to set it as "today" and see what each function returns, including calculated dollar amounts.

YTD
QTD
MTD

Logic Patterns

SWITCH, SELECTEDVALUE, and making reports dynamic

SWITCH: The Better IF Chain

Nested IFs (Messy)

IF([Revenue] > 1000000, "High",
  IF([Revenue] > 500000, "Medium",
    "Low"
  )
)
vs

SWITCH + VAR (Clean)

VAR Rev = [Revenue]
RETURN
  SWITCH( TRUE(),
    Rev > 1000000, "High",
    Rev > 500000, "Medium",
    "Low"
  )

Order matters: SWITCH returns the first match. Put restrictive conditions first. Storing [Revenue] in a VAR means it evaluates once, not twice.

Note: Field Parameters

Field parameters let users dynamically change which measure or column appears in a visual without any SWITCH logic. Created from the Modeling ribbon, they use NAMEOF() references under the hood. Power BI's slicer integration handles the rest.

SELECTEDVALUE: Slicer-Driven Logic

Returns a value when a column is filtered to exactly one value. Multi-select or no selection returns the fallback.

Slicer: Cycle

Revenue for All Cycles
= "Revenue for " & SELECTEDVALUE( 'Stardate'[Cycle], "All Cycles" )

Output: "Revenue for All Cycles"

Common Uses

Dynamic Titles

Report titles that change based on slicer selections. Great for exported PDFs.

What-If Parameters

SELECTEDVALUE reads the slider position and feeds it into calculations.

Measure Switching

One visual, multiple metrics via a disconnected slicer table.

Variables in DAX

Write once, use many: cleaner code and better performance

Variables store intermediate results, give them names, and prevent recalculation. You just saw SWITCH reference [Revenue] twice in Logic Patterns. With a variable, that value is calculated once and reused every time.

Revenue Band =
VAR CurrentRevenue = [Total Revenue]
VAR Result =
    SWITCH( TRUE(),
        CurrentRevenue > 5000000, "High",
        CurrentRevenue > 1000000, "Mid",
        CurrentRevenue > 0, "Low",
        "No Sales"
    )
RETURN Result

Variables capture context at definition time. CurrentRevenue is evaluated once and referenced in three SWITCH conditions. Without the variable, [Total Revenue] would recalculate for each condition check.

Why Use Variables?

Performance Evaluated once, reused everywhere.
Readability Named values replace repeated expressions.
Debugging Change RETURN to any VAR name to inspect values.

Step Through the Evaluation

VAR CurrentRevenue = [Total Revenue]
VAR Result = SWITCH( TRUE(), ... )
  CurrentRevenue > 5000000 // FALSE
  CurrentRevenue > 1000000 // TRUE -> "Mid"
RETURN Result
Revenue Band
--
Click "Step" to walk through how DAX evaluates this measure. Each variable is computed once and reused.
Step 0 of 5

Relationships & USERELATIONSHIP

Multiple date columns, one active relationship, and how to switch

Your Trades table has a TradeDate and a DeliveryDate. Both connect to Stardate. But Power BI only allows one active relationship between two tables. The fix: create inactive relationships and activate them on demand with USERELATIONSHIP.

Active vs Inactive Paths

Toggle a path to switch the active relationship and see how the numbers shift.

Stardate
Date
Year
Quarter
1
* Active: TradeDate
1
* Inactive: DeliveryDate
Trades
TradeDate
DeliveryDate
Credits
$1.5M $1.0M $0.5M 0
$1.34M
$1.18M
$1.29M
$1.37M
$1.41M
$1.30M
$1.05M
$1.23M
Q1 Q2 Q3 Q4
Trade Date Delivery Date
Total Revenue = SUM( 'Trades'[Credits] )
// Uses default active relationship (TradeDate)
i
Instead of USERELATIONSHIP, many teams use a Calculation Group to switch date paths. See the Calculation Groups section below.

Calculation Groups

Write time intelligence once, apply it to every measure

Think of a calculation group as a modifier that wraps around any measure. You write your base measures once (Revenue, Cost, Profit), then the calculation group applies transformations like YTD, Prior Cycle, or YoY% to whichever measure is in the visual. Instead of 3 measures x 3 time views = 9 measures, you maintain 3 base measures + 1 calc group.

The Duplication Problem

Without Calc Groups

Revenue YTD Cost YTD Profit YTD Revenue PY Cost PY Profit PY Revenue YoY% Cost YoY% Profit YoY%
9 measures (and growing)
vs

With Calc Groups

Revenue Cost Profit
×
Actual YTD Prior Cycle YoY %
3 base × 4 items = all combos

SELECTEDMEASURE()

The function that makes it work. It returns whichever measure the user placed in the visual:

// This ONE item applies YTD to ANY measure
YTD = CALCULATE( SELECTEDMEASURE(), DATESYTD('Stardate'[Date]) )

See It in Action

Select a calculation item to see how it transforms the base measure. Notice the format change on YoY%.

Base Measure
[Total Revenue]
×
Calculation Item
Currency
$5,082,000
// Actual: No modification
SELECTEDMEASURE()
The Actual item returns the measure unchanged. SELECTEDMEASURE() evaluates [Total Revenue] directly.

Under the Hood

How the DAX engine processes your queries in two stages

Every time a visual renders, DAX runs a two-stage process. Understanding this changes how you think about performance.

Storage Engine

Retrieves data from the in-memory VertiPaq model. Columnar, compressed, parallelized across cores.

Formula Engine

Evaluates DAX expressions: context logic, CALCULATE, iterators, variables. Single-threaded.

Result

Final value for your visual. More work in Stage 1 = faster overall.

Fast vs Slow Patterns

Storage Engine Friendly Fast

  • Simple aggregations (SUM, COUNT, MIN, MAX)
  • Star schema with clean foreign keys
  • Direct column references
  • CALCULATE with table filters

Formula Engine Heavy Slower

  • Complex iterators (SUMX with nested logic)
  • Nested CALCULATE inside CALCULATE
  • Row-by-row processing patterns
  • Many-to-many without bridge tables

The performance rule of thumb: Push as much work to the Storage Engine as possible. Star schema + simple aggregations = the Storage Engine does the heavy lifting = fast. When a measure feels slow, ask: "Can I restructure this to let the Storage Engine do more?"

Key Takeaways

Three parts, one complete foundation

Time Intelligence Returns Tables

DATEADD, SAMEPERIODLASTYEAR, and friends return date tables that CALCULATE uses as filters, not scalar values.

Variables Capture Context at Definition

Write once, reuse many times. The value is frozen when VAR is declared, not when it is referenced.

USERELATIONSHIP Activates Inactive Paths

One active relationship per pair of tables; switch to alternates inside CALCULATE on demand.

Calculation Groups Eliminate Duplication

One calc group with items (YTD, PY, YoY%) replaces dozens of nearly identical measures.

Push Work to the Storage Engine

Simple aggregations on star schema are parallelized and fast. Complex DAX runs single-threaded in the Formula Engine.

These Patterns Build on Context

Every technique here (time intel, variables, relationships, calc groups) relies on the filter context and CALCULATE foundations from Parts 1 and 2.

Tools for the Next Step

DAX Studio

Free external tool for writing/testing DAX queries with performance tracing and storage engine diagnostics.

Free Tool

DAX Query View

Built into Power BI Desktop. Write DAX queries and see results without any external installation.

Built-in

Performance Analyzer

Shows exactly how long each visual takes, split between storage engine and formula engine time.

Built-in
Back to Part 2

How DAX Thinks: Context is Everything

Filter context, row context, CALCULATE, iterators, and context transition.

Part 2 of 3
Restart: Part 1

Understanding DAX

The language, syntax, and how filters flow through relationships.

Part 1 of 3

You've worked through all three parts. The concepts you've internalized (context, CALCULATE, variables, time intelligence) are the same building blocks professionals use every day. Everything from here is applying these patterns to new problems. You've got the foundation. The rest is practice.

1 / 9

Ready to implement these DAX patterns in your models?

We help teams design and build production DAX, from time intelligence and calculation groups to performance-tuned measure layers.

Schedule a Call Our Offerings

Discussion

Loading comments...
**On screen:** AE logo, title "DAX Patterns," subtitle "Time, Variables, and Beyond," ~12 min read badge, and the three-part series nav showing this as Part 3. - Welcome to Part 3 of the DAX Fundamentals series: patterns that turn knowledge into solutions - Part 1 covered the language, Part 2 covered context and CALCULATE, this part is where it all gets applied - Today: time intelligence, logic patterns, variables, relationships, calculation groups, and how the engine works under the hood *Transition:* "Everything you've learned so far leads here. Let's start with how it all connects."
**On screen:** Section header "Building On Context." - This part isn't new theory; it's applying the foundations from Parts 1 and 2 - The patterns ahead all depend on filter context, CALCULATE, and the data model *Transition:* "Quick orientation, then into the topics."
**On screen:** "You've built the foundation. Now it's time to put it to work." - Single line setup: theory is done, this is the toolbelt - Four topics ahead, each building on context and CALCULATE *Transition:* "Here's the map of where we're going."
**On screen:** Blue Time Intelligence card. "Period comparisons: YTD, Prior Cycle, QTD." - First stop: comparing values across time periods - The most-requested pattern in business reporting *Transition:* "Next: writing cleaner formulas."
**On screen:** Purple Variables card. "Cleaner, faster, more debuggable formulas." - VAR / RETURN: store intermediate results, name them, reuse them - Performance, readability, and debuggability all improve at once *Transition:* "Then: when you need more than one date relationship."
**On screen:** Orange Relationships card. "Active, inactive, and virtual via USERELATIONSHIP." - Multiple dates per fact table (trade date, delivery date) is common - USERELATIONSHIP switches between them on demand inside CALCULATE *Transition:* "Finally: stop writing the same measure ten different ways."
**On screen:** Teal Calculation Groups card. "Eliminate duplication across your entire model." - Calc groups apply time intelligence (or any modifier) to ANY measure without duplication - Replace dozens of nearly-identical measures with one calc group *Transition:* "Let's start with time intelligence."
**On screen:** Section header "Time Intelligence." - The most common reporting need: comparing values across periods - DAX has dedicated functions for this; you don't have to roll your own date math *Transition:* "But first, one prerequisite."
**On screen:** Orange callout. "Prerequisite: A proper date table." Two example measures: Revenue YTD and Revenue Prior Cycle. - Time intelligence functions need a contiguous date table, marked as a date table in the model - These functions return tables of dates; CALCULATE uses them as filters - Notice the pattern: CALCULATE + a time intelligence function = period comparison *Key message:* No date table = no time intelligence. Get this right before anything else.
**On screen:** Function Reference accordion. Four panels: DATESYTD, DATESQTD, DATESMTD, DATEADD. - DATESYTD: Jan 1 through current date (annual cumulative) - DATESQTD / DATESMTD: same idea, quarter or month - DATEADD: the swiss-army knife. Shift dates by any interval (-1 YEAR, -1 QUARTER, etc.) - Click any panel to see syntax and an example *Transition:* "Let's see how each one behaves on real dates."
**On screen:** Interactive Stardate calendar (Jan-Mar 2226). YTD, QTD, MTD checkboxes and a bar chart of values. - Click any date to set it as "today" and watch each function return different ranges - YTD grows from Jan 1, QTD resets each quarter, MTD resets each month - The dollar amounts show how the calculated values change with the date selected *Transition:* "Time done. Next: logic that adapts."
**On screen:** Section header "Logic Patterns. SWITCH, SELECTEDVALUE, and making reports dynamic." - Two patterns this section: SWITCH for branching logic, SELECTEDVALUE for slicer-driven titles and parameters - Both let reports respond to data and user selections without nested IFs everywhere *Transition:* "Start with SWITCH."
**On screen:** Heading "SWITCH: The Better IF Chain." - Nested IFs get ugly fast: hard to read, hard to maintain - SWITCH is the cleaner replacement, especially with the SWITCH(TRUE(), ...) pattern *Transition:* "Side by side, the contrast is obvious."
**On screen:** Left card "Nested IFs (Messy)." Three-level nested IF with [Revenue] referenced twice. - This works, but every level adds cognitive load - Also note: [Revenue] is evaluated twice. Performance cost on large models *Transition:* "Same logic, written the right way."
**On screen:** Right card "SWITCH + VAR (Clean)." VAR captures [Revenue], SWITCH(TRUE(), ...) walks the conditions. - VAR Rev = [Revenue] evaluates once; the SWITCH reuses it - SWITCH(TRUE(), condition, result, ...) reads like a list - Add a fourth condition? One line, no extra nesting *Key message:* SWITCH(TRUE()) + VAR is the standard pattern for branching logic in DAX.
**On screen:** Caption "Order matters: SWITCH returns the first match. Put restrictive conditions first." - SWITCH stops at the first TRUE condition - Order from most restrictive to least: 1M, 500K, default - The VAR captures the value once so the comparison is fast even on large tables *Transition:* "One related concept before we move on."
**On screen:** Orange gotcha-card "Note: Field Parameters." - Sometimes you don't need SWITCH at all. Field Parameters let users swap measures via a slicer - Created from the Modeling ribbon, they use NAMEOF() under the hood - Use them for "let users pick which metric to show" instead of writing SWITCH logic by hand *Transition:* "Now: making titles and parameters react to slicers."
**On screen:** SELECTEDVALUE demo. Slicer with All / 2223 / 2224 / 2225 / 2226. Title updates live. Common Uses panel on the right. - SELECTEDVALUE returns the value when a column is filtered to exactly one selection - Multi-select or no selection returns the fallback ("All Cycles" here) - Three common uses: dynamic titles, what-if parameters, measure switching with disconnected slicers *Transition:* "Click around to see it react. Then on to Variables."
**On screen:** Section header "Variables in DAX. Write once, use many." - VAR / RETURN is the single biggest readability and performance upgrade you can make to your DAX - Three benefits: performance, readability, debuggability *Transition:* "Here's the canonical example."
**On screen:** Code on the left: Revenue Band measure with CurrentRevenue VAR feeding a SWITCH. Right side: orange callout plus a "Why Use Variables?" panel listing Performance, Readability, Debugging. - CurrentRevenue is evaluated ONCE, then referenced in three SWITCH conditions - Without the VAR, [Total Revenue] would recalculate three times - The three benefits: faster, cleaner names, and you can debug by changing RETURN to any VAR *Key message:* Variables capture context at definition time. That's the whole game.
**On screen:** "Step Through the Evaluation" interactive walker. Code lines highlight one by one; a summary card on the right shows the running value of Revenue Band. - Click Next to walk through the evaluation line by line - Watch how CurrentRevenue is computed once, then each SWITCH condition uses it - The summary card shows the final result building up *Transition:* "When one date relationship isn't enough: USERELATIONSHIP."
**On screen:** Section header "Relationships and USERELATIONSHIP." - Real fact tables often have multiple date columns (order date, ship date, delivery date) - Power BI allows only ONE active relationship between two tables - USERELATIONSHIP is how you switch to the others on demand *Transition:* "Here's the setup."
**On screen:** Paragraph explaining the Trades table has TradeDate AND DeliveryDate, both connecting to Stardate, but only one active at a time. - The classic problem: you want both views (trade and delivery) but the model can only have one default - Create the second relationship as INACTIVE, then activate it inside CALCULATE with USERELATIONSHIP *Transition:* "Toggle to see what happens."
**On screen:** Interactive data model on the left (Stardate to Trades, two relationship lines), toggle buttons "Trade Date Path" and "Delivery Date Path," bar chart on the right showing quarterly revenue under each path. Teal callout below. - Click each path to switch the active relationship and watch the chart update - The Q-by-Q numbers shift because revenue lands in different quarters depending on which date you use - Callout: a Calculation Group is often the cleaner way to do this. Sets up the next section *Transition:* "Speaking of calculation groups..."
**On screen:** Section header "Calculation Groups. Write time intelligence once, apply it to every measure." - This is the big one. Calc groups eliminate measure duplication across your entire model - One calc group can apply YTD, PY, YoY% to ANY measure in your model *Transition:* "Here's why this matters."
**On screen:** "The Duplication Problem." Two cards side by side. Left (red): nine separate measures (Revenue YTD, Cost YTD, ...). Right (teal): 3 base measures times 4 calc items = all combinations. - Without calc groups: 3 measures times 3 time views = 9 measures, and that grows with every base measure added - With calc groups: 3 base measures + 1 calc group with 4 items = same coverage, way less to maintain *Key message:* Calc groups turn an N-times-M measure explosion into N + M.
**On screen:** "SELECTEDMEASURE()." Code shows a YTD calc item: CALCULATE(SELECTEDMEASURE(), DATESYTD(...)). - SELECTEDMEASURE() is the function that makes this work - It returns whichever measure the user dropped in the visual - So this ONE calc item applies YTD to Revenue, Cost, Profit, or any other measure automatically *Transition:* "Let's see it in action."
**On screen:** "See It in Action" demo. Top row: Base Measure [Total Revenue], Calculation Item buttons (Actual / YTD / Prior Cycle / YoY %), Result card. Bottom row: formula code block + orange explanation panel. - Click each calculation item to see how the formula and the result change - Same base measure ([Total Revenue]), four different transformations - Notice on YoY %: the format string switches to percentage automatically. Calc groups can override format strings too *Transition:* "Now the engine bit, then we wrap."
**On screen:** Section header "Under the Hood. How the DAX engine processes your queries in two stages." - Performance-curious section. Optional for beginners but pays off when measures get slow - The mental model here changes how you write DAX *Transition:* "Two stages every query goes through."
**On screen:** "Every time a visual renders, DAX runs a two-stage process." - Every measure goes through the same pipeline - Once you see the pipeline, performance decisions become obvious *Transition:* "Here are the stages."
**On screen:** Three-card flow: Storage Engine (teal, columnar/compressed/parallel), arrow, Formula Engine (orange, single-threaded DAX evaluation), arrow, Result (blue). - Storage Engine: in-memory VertiPaq, columnar, compressed, parallelized across cores. FAST - Formula Engine: evaluates DAX expressions, CALCULATE, iterators, variables. SINGLE-THREADED - The rule of thumb: more work in the Storage Engine = faster query overall *Transition:* "Concretely: what's fast, what's slow."
**On screen:** Fast vs Slow Patterns. Two cards. Left "Storage Engine Friendly (Fast)": simple aggregations, star schema, direct columns, CALCULATE with table filters. Right "Formula Engine Heavy (Slower)": SUMX with nested logic, nested CALCULATE, row-by-row, many-to-many without bridges. - Fast patterns push work to the parallel side - Slow patterns force the single-threaded engine to do row-by-row work - When a measure feels slow, ask: "Can I restructure to let Storage Engine do more?" *Transition:* "One rule of thumb to remember."
**On screen:** Orange callout. "The performance rule of thumb: Push as much work to the Storage Engine as possible." - Star schema + simple aggregations = fast - Complex DAX + flat tables = slow - When in doubt, simplify the model before optimizing the DAX *Transition:* "Final section: pulling it all together."
**On screen:** Section header "Key Takeaways. Three parts, one complete foundation." - We're wrapping the entire DAX Fundamentals series here - Six key ideas to lock in, then resources for what's next *Transition:* "First recap card."
**On screen:** Tip card "Time Intelligence Returns Tables." - DATEADD, SAMEPERIODLASTYEAR, etc. don't return numbers; they return tables of dates - CALCULATE consumes those tables as filters - Knowing this stops a lot of "why is my measure broken" debugging *Transition:* "Next card."
**On screen:** Tip card "Variables Capture Context at Definition." - The value is frozen when VAR is declared, not when it's referenced - That's both the feature AND the gotcha - If you need the current context at reference time, use a measure, not a VAR *Transition:* "Next."
**On screen:** Tip card "USERELATIONSHIP Activates Inactive Paths." - One active relationship per pair of tables: that's the rule - USERELATIONSHIP inside CALCULATE switches to alternates on demand - Calc groups are often a cleaner way to do the same thing *Transition:* "Next."
**On screen:** Tip card "Calculation Groups Eliminate Duplication." - One calc group with YTD, PY, YoY% replaces dozens of nearly identical measures - Works on any measure thanks to SELECTEDMEASURE() - If you maintain a model with 50+ measures, this WILL change how you build *Transition:* "Next."
**On screen:** Tip card "Push Work to the Storage Engine." - Simple aggregations on a star schema = parallelized = fast - Complex DAX runs single-threaded in the Formula Engine - When a measure is slow, the fix is usually in the model, not the DAX *Transition:* "Last one."
**On screen:** Tip card "These Patterns Build on Context." - Every technique in Part 3 relies on filter context and CALCULATE from Parts 1 and 2 - If something here didn't click, the fix is almost always in the context fundamentals - Loop back to Part 2 anytime you hit a wall *Transition:* "Tools to take with you."
**On screen:** "Tools for the Next Step." Three cards: DAX Studio (Free Tool), DAX Query View (Built-in), Performance Analyzer (Built-in). - DAX Studio: free external tool, best for tracing and storage engine diagnostics - DAX Query View: built into Power BI Desktop, write queries without installing anything - Performance Analyzer: shows query time split between Storage Engine and Formula Engine - Click any card to open the official docs *Transition:* "And we close."
**On screen:** Closing paragraph: "You've worked through all three parts..." - All three parts done. Context, CALCULATE, variables, time intelligence: these are the building blocks pros use daily - Everything from here is applying these patterns to new problems - Thank you and goodnight: the rest is practice *Closing:* "Questions?"