GENERATE
For each row of the first table, evaluates a second table expression and returns the combined rows, dropping first-table rows that produce nothing.
GENERATE(<table1>, <table2>)Returns: A table.
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
CALCULATETABLEwhen it uses measures, so the outer row acts as a filter.
Example
The top three product categories for each store, by sales.
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.
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.
GENERATEALLkeeps 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
CALCULATEorCALCULATETABLEto 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,
CROSSJOINis 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.