Understanding GENERATE in DAX with Practical Examples

What Is GENERATE in DAX?

GENERATE combines two table expressions by evaluating the second table expression for each row of the first table.
This is the key idea to understand when working with GENERATE.

The basic syntax is:

GENERATE(
    <First Table>,
    <Second Table Expression>
)

The second table expression can access the current row of the first table. This makes GENERATE particularly useful when the second table needs to be dynamically filtered based on values from the first table.

1. Retrieve the Student Names

Before understanding GENERATE, the query first retrieves the student names from the students table.

Query

EVALUATE
    SELECTCOLUMNS(students, students[NAME])

Step-by-step explanation

Step 1: EVALUATE
EVALUATE returns the result of a table expression.

Step 2: SELECTCOLUMNS
SELECTCOLUMNS creates a new table containing the column specified from the source table.

Step 3: students[NAME]
This is the student name column that we want to return.

What will you get?

The result is a single-column table containing the names from the students table.

The names above are only for illustration. Your actual output depends on the data in your students table.

2. Retrieve the Payments Table

The next query simply returns the payments table.

Query

EVALUATE
    payments

Step-by-step explanation

Step 1: EVALUATE
DAX returns the table expression provided after EVALUATE.

Step 2: payments
The complete payments table is used as the table expression.

What will you get?

You will get all rows and columns available in the payments table.

The example values are illustrative and may differ from your practice data.

3. GENERATE Used Like a CROSS JOIN

Now we can combine the student names with the complete payments table using GENERATE.

Query

EVALUATE
    GENERATE(
        SELECTCOLUMNS(students, students[NAME]),
        payments
    )

Step-by-step explanation

Step 1: First table expression
The first argument is:

SELECTCOLUMNS(students, students[NAME])

This produces the list of student names. GENERATE then processes this table one row at a time.

Step 2: Second table expression
The second argument is:

payments

Because the second expression is simply the complete payments table and does not use the current student row, the same payment rows are generated for every student.

This produces a result that behaves like a CROSS JOIN: every row from the first table is combined with every row from the second table.

Conceptual example

The example above demonstrates the combination concept. The exact number of rows depends on the number of rows in each table.

4. GENERATE with Filtering Based on the First Table

The real power of GENERATE becomes clearer when the second table expression uses a value from the current row of the first table.

Query

EVALUATE
    GENERATE(
        SELECTCOLUMNS(students, students[ID], students[NAME]),
        SELECTCOLUMNS(
            FILTER(payments, payments[Student] = students[ID]), 
            payments[Paid]
        )
    )

Step-by-step explanation

Step 1: Create the first table
The first argument is:

SELECTCOLUMNS(students, students[ID], students[NAME])

This creates a table containing the student’s ID and NAME.

Step 2: GENERATE processes each student row
GENERATE evaluates the second table expression separately for each row of this first table.

For the current student row, the value of students[ID] is available to the second table expression.

Step 3: FILTER the payments table
The second table expression contains:

FILTER(
    payments,
    payments[Student] = students[ID]
)

The FILTER function checks every payment and keeps only the rows where payments[Student] matches the ID of the current student.

Step 4: SELECTCOLUMNS returns Paid
After filtering the payments, SELECTCOLUMNS returns only:

payments[Paid]

Therefore, each student is combined only with that student’s matching payment records.

How GENERATE evaluates this query

1. Take the first student.
2. Read that student’s ID.
3. Filter the payments table using that ID.
4. Return the matching Paid values.
5. Move to the next student.
6. Repeat the same process.

Conceptual result

The result above is only an illustration of the logic. Your actual output depends on the relationships between the values in students[ID] and payments[Student].

5. Understanding the Difference Between the Two GENERATE Examples

6. GENERATE vs CROSS JOIN Concept

Both patterns can produce combinations of rows, but the important difference is how the second table expression is evaluated.

7. Removing Duplicate Student Names with DISTINCT

The final query uses DISTINCT to return only unique student names.

Query

EVALUATE
    DISTINCT(students[NAME])

Step-by-step explanation

Step 1: DISTINCT
DISTINCT removes duplicate values from the supplied column or table expression.

Step 2: students[NAME]
Here, the expression supplies the student name column.

The result contains each unique student name only once.

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist