CROSSJOIN
Returns every combination of rows from the tables you give (a cartesian product).
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
- tables: two or more tables. The row counts multiply.
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
- Row counts multiply. Two lists of 1,000 rows make one million rows.
- Combinations with no data still appear. Add a measure to show zeros, or use
SUMMARIZECOLUMNSto drop empty groups. - Columns from different tables that share a name are renamed with the table name.
- When the second list depends on the first, use
GENERATEinstead. - Test with a small list first, then add the full list.
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.