Downloads
DAXTable constructionIntermediate

CROSSJOIN

Returns every combination of rows from the tables you give (a cartesian product).

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

Returns: A table. Columns from each table are kept, with names made unique.

What it does

CROSSJOIN pairs every row of one table with every row of the next. Three countries and two channels give six rows. Use it to build a full grid of combinations for a planning table, so every country and channel pair exists even when it has no sales. The row count grows quickly, so keep the inputs short.

Syntax and arguments

Example

A grid of every country and channel pair, with sales for each. Pairs with no sales show a blank or zero, which is the point of the grid.

EVALUATE
ADDCOLUMNS (
    CROSSJOIN (
        VALUES ( Stores[Country] ),
        VALUES ( Channel[ChannelName] )
    ),
    "Sales", [Total Sales]
)

The grid lists each combination once. A reviewer can see at a glance which country and channel pairs have no activity.

Example: a second use

A planning grid of each product category against two forecast scenarios, typed in as a small table.

Scenario Plan =
CROSSJOIN (
    DATATABLE ( "Scenario", STRING, { { "Base" }, { "Upside" } } ),
    VALUES ( Product[ProductCategory] )
)

Each category appears once under Base and once under Upside. The rows are ready for a forecast measure to fill in.

Things to watch

In business terms

Budget grids (every cost center by every month), scenario planning tables, and coverage reports where a missing combination is itself the finding.

Related functions

All DAX functions · View as Markdown · DAX fundamentals