CrossJoin

Returns the cross-product of two sets from different dimensions.

Syntax

CrossJoin ( set1, set2 )

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

Example 1

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:

Example 2

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:

Example 3

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: