The GENERATE / ROW pattern is an advanced but powerful DAX technique used to dynamically create rows and expand tables based on calculations. It is especially useful when you need to produce derived rows, combinations, or scenario-based expansions that don’t exist physically in your data model.

This article explains what the pattern is, when to use it, how it works, and provides practical examples. It assumes you are familiar with concepts such as row context, filter context, and iterators.

What Is the GENERATE / ROW Pattern?

At its core, the pattern combines two DAX functions:

• GENERATE() – Iterates over a table and returns a union of tables generated for each row.
• ROW() – Creates a single-row table with named columns and expressions.

Together, they allow you to:

• Loop over an outer table
• Generate one or more rows per input row
• Shape those rows using calculated expressions

In effect, this pattern mimics a nested loop or table expansion operation.

Why This Pattern Exists

DAX does not support procedural loops like for or while.
Instead, iteration happens through table functions.

GENERATE() fills a critical gap by allowing you to:

• Produce variable numbers of rows per input row
• Apply row-level calculations while preserving relationships and context

Function Overview

GENERATE

GENERATE (
    table1,
    table2
)

• table1: The outer table being iterated.
• table2: A table expression evaluated for each row of table1.

The result is a flattened table containing all rows returned by table2 for every row in table1.

ROW

ROW (
    "ColumnName1", Expression1,
    "ColumnName2", Expression2
)

• Returns a single-row table
• Expressions are evaluated in the current row context

When Should You Use the GENERATE / ROW Pattern?

This pattern is ideal when:

✅ You Need to Create Derived Rows

Examples:

• Generating “Start” and “End” rows per record
• Creating multiple event types per transaction

✅ You Need Scenario or Category Expansion

Examples:

• Actual vs Forecast vs Budget rows
• Multiple pricing or discount scenarios

✅ You Need Row-Level Calculations That Produce Rows

Examples:

• Expanding date ranges into multiple calculated milestones
• Generating allocation rows per entity

❌ When Not to Use It

• Simple aggregations → use SUMX, ADDCOLUMNS
• Static lookup tables → use calculated tables or Power Query
• High-volume fact tables without filtering (can be expensive)

Basic Example: Expanding Rows with Labels

Scenario

You have a Sales table:

You want to generate two rows per order:

• One for Gross
• One for Net (90% of gross)

DAX Code

Sales Breakdown =
GENERATE (
    Sales,
    ROW (
        "Type", "Gross",
        "Value", Sales[Amount]
    )
    &
    ROW (
        "Type", "Net",
        "Value", Sales[Amount] * 0.9
    )
)

Result

Key Concept: Context Transition

Inside ROW():

• You are operating in row context
• Columns from the outer table (Sales) are directly accessible
• No need for EARLIER() or variables in most cases

This makes the pattern cleaner and easier to reason about.

Intermediate Example: Scenario Modeling

Scenario

You want to model multiple pricing scenarios for each product.

Scenarios:

• Standard (100%)
• Discounted (90%)
• Premium (110%)

DAX Code

Product Pricing Scenarios =
GENERATE (
    Products,
    UNION (
        ROW ( "Scenario", "Standard",   "Price", Products[BasePrice] ),
        ROW ( "Scenario", "Discounted", "Price", Products[BasePrice] * 0.9 ),
        ROW ( "Scenario", "Premium",    "Price", Products[BasePrice] * 1.1 )
    )
)

Result

Advanced Example: Date-Based Expansion

Scenario

For each project, generate two milestone rows:

• Start Date
• End Date

DAX Code

Project Milestones =
GENERATE (
    Projects,
    UNION (
        ROW (
            "Milestone", "Start",
            "Date", Projects[StartDate]
        ),
        ROW (
            "Milestone", "End",
            "Date", Projects[EndDate]
        )
    )
)

This is especially useful for timeline visuals or event-based reporting.

Performance Considerations ⚠️

The GENERATE / ROW pattern can be computationally expensive.

Best Practices

• Filter the outer table as early as possible
• Avoid using it on very large fact tables
• Prefer calculated tables over measures when expanding rows
• Test with realistic data volumes

Common Mistakes

❌ Using GENERATE When ADDCOLUMNS Is Enough

If you’re only adding columns—not rows—ADDCOLUMNS() is simpler and faster.

❌ Forgetting Table Shape Consistency

All ROW() expressions combined with UNION() must return the same column structure.

❌ Overusing It in Measures

This pattern is usually better suited for calculated tables, not measures.

Mental Model to Remember

Think of the GENERATE / ROW pattern as:

“For each row in this table, generate one or more calculated rows and stack them together.”

If that sentence describes your problem, this pattern is likely the right tool.

Final Thoughts

The GENERATE / ROW pattern is one of those DAX techniques that feels complex at first—but once understood, it unlocks entire classes of modeling and analytical solutions that are otherwise impossible.

Used thoughtfully, it can replace convoluted workarounds, reduce model complexity, and enable powerful scenario-based reporting.

Thanks for reading!