GENERATEALL in DAX: Create a Cartesian Product with Matching Rows

What Is GENERATEALL in DAX?

GENERATEALL combines two table expressions by evaluating the
second table expression for every row of the first table.
The rows returned by the second table are then combined with the current row
of the first table.

The important difference between GENERATE and
GENERATEALL is what happens when the second table returns
no rows. GENERATEALL keeps the row from the first table
and returns blank values for the columns coming from the
second table.

Query

// GENERATE ALL - Cartesian Product of Table Rows Based on First Table
// and retrieve blank rows for no matches on 2nd Table

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

Understanding the Tables

In this example, we are working with two tables:

• students — contains the list of students.
• payments — contains payment records made by students.

The relationship between the two tables in this query is established through
students[ID] and payments[Student].

The payments table can contain multiple payment records for
the same student.

Step-by-step Explanation

Step 1: EVALUATE

EVALUATE tells DAX to return the result of the table
expression that follows it.

EVALUATE
    GENERATEALL(
        ...
    )

Since GENERATEALL returns a table, it can be used directly
after EVALUATE.

Step 2: The First Table — students

The first argument of GENERATEALL is the
students table.

GENERATEALL(
    students,
    ...
)

DAX processes the rows of the students table one at a time.
For each student row, the second table expression is evaluated.

Step 3: FILTER the payments Table

The second argument uses FILTER to find payment records
belonging to the current student.

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

The condition compares payments[Student] with
students[ID].

In other words, for the current student, DAX looks through the
payments table and keeps only the payment rows where
the student IDs match.

Step 4: Combine the Matching Rows

GENERATEALL combines the current row from
students with every row returned by the filtered
payments table.

Therefore, if a student has multiple payment records, that student can
appear multiple times in the result.

How the Cartesian Product Works

Consider Student 101. Suppose this student has two payment
records: ₹5,000 and ₹2,500.

The filtered payment table returns both rows. GENERATEALL
combines the student row with each matching payment row.

This is why GENERATEALL can produce multiple output rows
for a single row in the first table.

What Happens When There Is No Matching Payment?

This is the important part of GENERATEALL.

Suppose Student 102 does not have any matching row in the
payments table.

The FILTER expression returns an empty table for Student 102.
Unlike GENERATE, GENERATEALL still retains
the row from the first table and returns blank values for
the columns contributed by the second table.

Understanding the Query Flow

1. DAX starts with the students table.
2. DAX takes one student row as the current row.
3. The FILTER function searches the payments table.
4. Only payment rows where payments[Student] = students[ID] are returned.
5. GENERATEALL combines the student row with every matching payment row.
6. If there are no matching payment rows, the student row is still retained with blank payment columns.
7. DAX repeats the process for every row in the students table.

GENERATE vs GENERATEALL

Understanding the FILTER Condition

payments[Student] = students[ID]

This condition is evaluated while GENERATEALL is processing
each row of the students table.

For example, when the current student ID is 101, the
condition effectively looks for payment rows where:

payments[Student] = 101

When the current student changes to 103, the same expression
evaluates against that student’s ID.

payments[Student] = 103

Important Notes

1. The first table controls the base rows

The students table is the first argument, so its rows form
the base of the generated result.

2. The second table can return multiple rows

A student can have multiple payment records. Each matching payment creates
another combined row in the result.

3. No-match rows are retained

With GENERATEALL, a student without a matching payment is
still included, with blank values for the columns coming from
payments.

4. GENERATEALL returns a table

Because GENERATEALL produces a table expression, it can be
returned directly using EVALUATE in a DAX query.

EVALUATE
    GENERATEALL(
        students,
        FILTER(
            payments,
            payments[Student] = students[ID]
                && payments[Payment Amount] > 3000
        )
    )

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist