<!-- https://learnsql.cpetoday.com/functions/dax/generate -->
# GENERATE

Language: DAX
Category: Table construction
Syntax: GENERATE(<table1>, <table2>)
Summary: For each row of the first table, evaluates a second table expression and returns the combined rows, dropping first-table rows that produce nothing.
Returns: A table.
Level: Advanced
Related: GENERATESERIES, CROSSJOIN, CALCULATETABLE, TOPN, VALUES, ADDCOLUMNS

## What it does
GENERATE loops over the rows of one table. For each row, it evaluates a second table expression that can use that row's values. The results are stacked into one table. It is the DAX equivalent of running a small query once for each row. A common use is "the top three product categories for each store." Rows from the first table that produce no rows in the second are dropped. `GENERATEALL` keeps them.

## Syntax and arguments
- **table1**: the outer table, whose rows drive the loop.
- **table2**: a table expression evaluated for each outer row. Wrap it in `CALCULATETABLE` when it uses measures, so the outer row acts as a filter.

## Example
The top three product categories for each store, by sales.

```dax
Top Categories by Store =
GENERATE (
    VALUES ( Stores[StoreName] ),
    CALCULATETABLE (
        TOPN ( 3, VALUES ( Product[ProductCategory] ), [Total Sales], DESC )
    )
)
```

For each store, `CALCULATETABLE` filters the sales to that store, and `TOPN` picks the three categories with the highest sales. The result lists each store with its three categories.

## Example: a second use
Each customer's single largest product by sales.

```dax
Customer Top Product =
GENERATE (
    VALUES ( Customer[CustomerKey] ),
    CALCULATETABLE (
        TOPN ( 1, VALUES ( Product[ProductName] ), [Total Sales], DESC )
    )
)
```

The customer key filters the sales, so each customer gets their own top product. This is a fixed list at refresh, which suits a review table rather than a live visual.

## Things to watch
- First-table rows with no match in the second table are removed. `GENERATEALL` keeps them.
- The inner expression runs once for each outer row. Keep the outer table to distinct members, not to sales lines.
- Measures in the inner table need `CALCULATE` or `CALCULATETABLE` to turn the outer row into a filter.
- Ties at the top can return more rows than the number you asked for.
- For every combination of two lists, `CROSSJOIN` is simpler.

## In business terms
Top N per segment for a management report, the largest vendor in each cost center, or the main product of each customer for a sales review. It also answers "what are the top three items in each group?" questions in an audit or a close review.
