CROSSJOIN
Every combination of the rows of two or more tables.
Group: Table manipulation. Compatible with DAX.
Syntax
CROSSJOIN ( <table1>, <table2> [, <table>] … )| Parameter | Type | Description |
|---|---|---|
table1 | Table | The first table. |
table2 | Table | The second table. |
table | Table optional, repeatable | Further tables, each multiplying the rows again. |
Returns
Table. Every row of the first table paired with every row of the second (and of the next), with the columns of all of them.
Remarks
- The number of rows is the product of the tables' row counts: 4 products and 16 places make 64 rows. One empty table makes the result empty.
- Two columns of the same name are an error. Tables written in the query need different column names; rename one with
SELECTCOLUMNS. - It does not look at the data, so it lists combinations that never occur. For the ones that do, use
SUMMARIZEorSUMMARIZECOLUMNS. - Model columns stay tied to the model, so a CROSSJOIN of column values works as a filter of combinations in
CALCULATE.
Examples
Every size for every product
EVALUATE
CROSSJOIN (
DATATABLE ( "Size", STRING, { { "33 cl" }, { "50 cl" } } ),
DATATABLE ( "Product", STRING, { { "Pils" }, { "IPA" }, { "Stout" } } )
)
ORDER BY [Product], [Size]| Size | Product |
|---|---|
| 33 cl | IPA |
| 50 cl | IPA |
| 33 cl | Pils |
| 50 cl | Pils |
| 33 cl | Stout |
| 50 cl | Stout |
Combinations that never occur
Depots sell nothing, yet CROSSJOIN pairs them with both categories.
EVALUATE
CROSSJOIN ( VALUES ( Products[Category] ), VALUES ( Places[Kind] ) )
ORDER BY Products[Category], Places[Kind]| Products[Category] | Places[Kind] |
|---|---|
| Beer | Depot |
| Beer | Shop |
| Cider | Depot |
| Cider | Shop |
Related functions
GENERATE, SUMMARIZE, NATURALINNERJOIN, UNION, SUMMARIZECOLUMNS.