Crossjoin function (MDX)

The Crossjoin function returns the cross product of the members of two sets.

Returns: A set.

Syntax


>>-Crossjoin--(--SetExpression1--,--SetExpression2--)----------><

Restriction: The crossjoin operator syntax SetExpression1*SetExpression2 is not supported.

Parameters

SetExpression1: Specifies the set to crossjoin with the second set.
SetExpression2: Specifies the set to crossjoin with the first set.

Description

Use the Crossjoin function to produce a data set that presents all member combinations of two sets. The sets must exist in separate hierarchies. You can use the Crossjoin function to create a new set that places two hierarchies on the same axis. By nesting one hierarchy inside another, you reduce the coding required to obtain complex result sets.

In your query, the order of the sets is important because it affects the presentation of the resulting data set. The order of the tuples in the resulting data set follows the order of the members within the sets that you crossjoin.

For example, if you apply the Crossjoin function to the following two sets ({a, b, c}, {d, e}), it yields the following result set: {(a,d), (a, e), (b, d), (b, e), (c, d), (c, e)}.

You can apply the Crossjoin function to only two hierarchies at a time. To apply the Crossjoin function to sets from three hierarchies, you must nest the Crossjoin functions.

However, if your sets have more than one hierarchy, you can combine them. For example, if you apply the Crossjoin function to the following sets ( {(dim1.a, dim2.b), (dim1.a, dim2.c) }, {(dim3.d, dim4.e), (dim3.f, dim4.e) }, it yields the following result set { (dim1.a, dim2.b, dim3.d, dim4.e), (dim1.a, dim2.b, dim3.f, dim4.e), (dim1.a, dim2.c, dim3.d, dim4.e), (dim1.a, dim2.c, dim3.f, dim4.e)}

Important: Crossjoins between sets within large cubes can negatively affect performance. Because the Crossjoin function returns all combinations between every member within the two sets, the resulting data set can be quite large. For example, if you crossjoin two sets that contain 150 members each, the resulting data set will contain 22500 tuples.

Examples

The following example uses the Crossjoin function to retrieve profit amount by year and store.

Query

SELECT {[Price Analysis].[Measures].[Profit Amount]} 

   ON AXIS(0), 

   {Crossjoin([Price Analysis].[Time].[All Time (Calendar)].Children, 

   Descendants([Price Analysis].[Store].[All Stores], -1, LEAVES))} 

   ON AXIS(1)

FROM [Price Analysis]

Result

Time	Store	Profit Amount
2002	ValueTrend Store 554	18254.86
	ValueTrend Store 782	21476.02
	ValueTrend Store 1414	24201.9
	ValueTrend Store 835	19641.34
	ValueTrend Store 1095	26162.7
	ValueTrend Store 1199	28487.65
	ValueTrend Store 116	27132.48
	ValueTrend Store 278	23881.1
	ValueTrend Store 375	24725.25
	ValueTrend Store 681	22614.47
	ValueTrend Store 875	26946.31
2003	ValueTrend Store 554	16169.29
	ValueTrend Store 782	22361.72
	ValueTrend Store 1414	25947.74
	ValueTrend Store 835	17755.49
	ValueTrend Store 1095	23596.06
	ValueTrend Store 1199	26445.33
	ValueTrend Store 116	25934.39
	ValueTrend Store 278	24863.64
	ValueTrend Store 375	24419.23
	ValueTrend Store 681	24822.08
	ValueTrend Store 875	22899.99
2004	ValueTrend Store 554	21936.34
	ValueTrend Store 782	25240.16
	ValueTrend Store 1414	30560.87
	ValueTrend Store 835	20792.24
	ValueTrend Store 1095	31914.34
	ValueTrend Store 1199	29880.26
	ValueTrend Store 116	28883.55
	ValueTrend Store 278	26441.61
	ValueTrend Store 375	28532.49
	ValueTrend Store 681	31714.76
	ValueTrend Store 875	27513.82

Related functions

In addition to the Crossjoin function, you can perform basic set operations by using the Union, Intersect, and Except.

The Edge and Nest functions are synonyms for the Crossjoin function.

Feedback | Information roadmap