Returns the cross-product of two sets from different dimensions.
Syntax
CrossJoin ( set1, set2 )
Parameter | Description |
---|---|
set1 | A set to cross with set2. |
set2 | A set to cross with set1. Must not include any dimension used in set1. |
Notes
This function returns the cross-product of two sets from different dimensions. If the two sets share a common dimension, an error is returned.
If one of the input sets is empty, the output set will be empty as well. For example, the output will be empty if the input set is [Root Beer].children but [Root Beer] has no children.
The order of the sets (and their constituent tuples) provided to the CrossJoin function have an effect on the order of the tuples in the result set. For example,
CrossJoin({a, b}, {c, d})
returns {(a, c), (a, d), (b, c), (b, d)}
CrossJoin({a, b, c}, {d, e, f})
returns {(a, d), (a, e), (a, f), (b, d), (b, e), (b, f), (c, d), (c, e), (c, f)}
Be aware of the order of the output set when using the results of CrossJoin with other order-dependent set functions; for example, Head or Tail.
Example
The following expression
CrossJoin({[Qtr1], [Qtr2]}, {[New York], [California]})
returns the set:
{([Qtr1], [New York]), ([Qtr1], [California]), ([Qtr2], [New York]), ([Qtr2], [California])}
Therefore, the following query
SELECT CrossJoin({[Qtr1], [Qtr2]}, {[New York], [California]}) ON COLUMNS FROM sample.basic
returns the grid:
Qtr1 | Qtr1 | Qtr2 | Qtr2 |
---|---|---|---|
New York | California | New York | California |
1656 | 3129 | 2363 | 3288 |
The following expression
CrossJoin({[Qtr1], [Qtr2], [Qtr3]}, {[New York], [California], [Texas]})
returns the set
{([Qtr1], [New York]), ([Qtr1], [California]), ([Qtr1], [Texas]), ([Qtr2], [New York]), ([Qtr2], [California]), ([Qtr2], [Texas]), ([Qtr3], [New York]), ([Qtr3], [California]), ([Qtr3], [Texas])}
Therefore, the following query
SELECT CrossJoin({[Qtr1], [Qtr2], [Qtr3]}, {[New York], [California], [Texas]}) ON AXIS(0) FROM Sample.Basic
returns the grid:
Qtr1 | Qtr1 | Qtr1 | Qtr2 | Qtr2 | Qtr2 | Qtr3 | Qtr3 | Qtr3 |
---|---|---|---|---|---|---|---|---|
New York | California | Texas | New York | California | Texas | New York | California | Texas |
1656 | 3129 | 1582 | 2363 | 3288 | 1610 | 1943 | 3593 | 1703 |
The following expression
CrossJoin ([100].children, [Profit].children)
returns the set:
{([100-10], Margin), ([100-10], [Total Expenses]), ([100-20], Margin), ([100-20], [Total Expenses]), ([100-30], Margin), ([100-30], [Total Expenses])}
Therefore, the following query
SELECT {([Market].levels(1).members)} ON COLUMNS, CrossJoin ([100].children, [Profit].children) ON ROWS FROM Sample.Basic
returns the grid:
(axis) | (axis) | East | West | South | Central |
---|---|---|---|---|---|
100–10 | Margin | 15762 | 8803 | 5937 | 8124 |
Total Expenses | 4633 | 4210 | 2361 | 4645 | |
100–20 | Margin | 1785 | 3707 | 2767 | 7426 |
Total Expenses | 671 | 4241 | 1570 | 3495 | |
100–30 | Margin | 871 | 1629 | #Missing | 3975 |
Total Expenses | 458 | 2139 | #Missing | 1895 |