Downloads
DAXTable constructionAdvanced

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.

Syntax
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

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

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.

Related functions

All DAX functions · View as Markdown · DAX fundamentals