CROSSJOIN in DAX: Create a Cartesian Product of Tables

What Is CROSSJOIN in DAX?

CROSSJOIN returns a table that contains the Cartesian product of all rows from the tables provided to the function.

In simple terms, every row from the first table is combined with every row from the second table.

For example, if the Students table contains 3 students and the Courses table contains 4 courses, the CROSSJOIN result will contain:

This is useful when you need to generate every possible combination between two sets of values.

1. View the Students Table

Query

EVALUATE
    students

Step-by-step explanation

Step 1: EVALUATE
EVALUATE is used to return the result of a table expression in a DAX query.

Step 2: students
students is the table expression. DAX returns the rows and columns available in the Students table.

What will you get?

You will get the existing records from the students table. For example:

The example data above is for illustration. Your actual result depends on the data in your Students table.

2. View the Courses Table

Query

EVALUATE
    courses

Step-by-step explanation

Step 1: EVALUATE
EVALUATE tells DAX to return the result of the table expression.

Step 2: courses
courses is the table expression that represents the Courses table.

What will you get?

You will get the existing course records from the courses table. For example:

3. Create a Cartesian Product Using CROSSJOIN

Query

EVALUATE
    CROSSJOIN(students, courses)

Step-by-step explanation

Step 1: CROSSJOIN
CROSSJOIN combines every row from the first table with every row from the second table.

Step 2: students
The students table is the first input table.

Step 3: courses
The courses table is the second input table.

DAX creates every possible combination between the rows in these two tables.

How does the Cartesian Product work?

Suppose we have 3 students and 4 courses:

The first student is combined with all courses, then the second student is combined with all courses, and so on.

Example Result

4. Select Specific Columns Before CROSSJOIN

Query

EVALUATE
    CROSSJOIN(
        SELECTCOLUMNS(students, students[NAME]),
        SELECTCOLUMNS(courses, courses[NAME])
    )

Step-by-step explanation

This query combines SELECTCOLUMNS with CROSSJOIN. Instead of passing the complete tables directly into CROSSJOIN, we first select the required columns from each table.

Step 1: SELECTCOLUMNS(students, students[NAME])
This creates a table expression containing the NAME column from the Students table.

Step 2: SELECTCOLUMNS(courses, courses[NAME])
This creates another table expression containing the NAME column from the Courses table.

Step 3: CROSSJOIN
CROSSJOIN then combines every row from the selected Students column with every row from the selected Courses column.

This approach is useful when the original tables contain many columns but you only need specific columns for the Cartesian product.

What will you get?

The result contains only the selected columns from both tables:

Understanding CROSSJOIN with SELECTCOLUMNS

Important Notes

1. CROSSJOIN can produce many rows

The number of rows returned by a CROSSJOIN is the product of the row counts of the input tables.

Rows in Table 1 × Rows in Table 2 = CROSSJOIN Result Rows

Therefore, CROSSJOIN should be used carefully when the input tables contain a large number of rows.

2. CROSSJOIN creates combinations, not matching rows

CROSSJOIN does not look for matching keys between the two tables. Instead, it generates every possible combination.

3. SELECTCOLUMNS can reduce the columns in the result

When you need only specific columns, using SELECTCOLUMNS before CROSSJOIN can make the resulting table easier to understand and work with.

4. Column names should be considered

When combining tables with CROSSJOIN, the resulting table must have uniquely named columns. Selecting and renaming columns can help avoid naming conflicts.

EVALUATE
    CROSSJOIN(
        SELECTCOLUMNS(students, "Student", students[NAME]),
        SELECTCOLUMNS(courses, "Course", courses[NAME])
    )