Daily DAX : Day 355 CROSSJOIN

Power BI DAX CROSSJOIN Function

Description

The CROSSJOIN function in Power BI DAX (Data Analysis Expressions) creates a new table that contains all possible combinations of rows from two or more input tables. It performs a Cartesian product, meaning every row from one table is paired with every row from the other table(s).

Syntax

CROSSJOIN(<table1>, <table2> [, <table3>, ...])

• <table1>, <table2>, ...: The tables or table expressions to combine.

Return Value

A table containing all possible combinations of rows from the input tables, with columns from each table included in the result.

Use Case

CROSSJOIN is useful when you need to analyze combinations of data from multiple tables, such as generating scenarios, creating matrices, or preparing data for further calculations. For example, it can be used to pair every product with every sales region to analyze potential sales opportunities.

Example

Suppose you have two tables:

• Products: Contains product names.
• Regions: Contains sales regions.

Sample Data

Products Table
Product
Laptop
Phone

Regions Table
Region
North
South

DAX Query

CombinedTable = CROSSJOIN(Products, Regions)

Result

Product	Region
Laptop	North
Laptop	South
Phone	North
Phone	South

Common Scenarios

• Scenario Analysis: Combine products and regions to forecast sales for each combination.
• Data Preparation: Generate a table of all possible combinations for further calculations, like budgeting or planning.
• Matrix Visuals: Create a matrix in Power BI by combining dimensions (e.g., products and time periods).

Notes

• CROSSJOIN can significantly increase the number of rows in the output table (e.g., 10 rows in table1 and 5 rows in table2 result in 50 rows).
• Use cautiously with large tables to avoid performance issues.
• It does not require a relationship between the tables.