CROSSJOIN (MDX)
Returns a set of tuples formed by the cross-product of the specified sets.
Returned Type
Syntax and Details
CROSSJOIN(set_expression1, set_expression2)
Where:
-
set_expression1 and set_expression2 are expressions that evaluate to sets of members.
Note that no more than one of these sets can contain measures or expressions that evaluate to numbers. If both sets contain measures or expressions that evaluate to numbers, the analytics engine issues the error Two measures cannot be crossjoined.
The function identifies all the members of each set and then generates a set of tuples that combine each member of the first set with each member of the second set.
Tip:
The keyword phrase NON EMPTY is particularly useful with this function. Note that you can use this keyword phrase immediately before any set expression.
Example
For example:
SELECT MEASURES.[%COUNT] ON 0, CROSSJOIN(diagd.MEMBERS, aged.[age group].MEMBERS) ON 1 FROM patients
Patient Count
1 None->0 to 29 3,839
2 None->30 to 59 3,615
3 None->60+ 971
4 asthma->0 to 29 308
5 asthma->30 to 59 282
6 asthma->60+ 113
7 CHD->0 to 29 1
8 CHD->30 to 59 93
9 CHD->60+ 229
10 diabetes->0 to 29 30
11 diabetes->30 to 59 246
12 diabetes->60+ 228
13 osteoporosis->0 to 29 *
14 osteoporosis->30 to 59 *
15 osteoporosis->60+ 200
In contrast, suppose that we add the NON EMPTY keyword phrase:
SELECT MEASURES.[%COUNT] ON 0, NON EMPTY CROSSJOIN(diagd.MEMBERS, aged.[age group].MEMBERS) ON 1 FROM patients
Patient Count
1 None->0 to 29 3,839
2 None->30 to 59 3,615
3 None->60+ 971
4 asthma->0 to 29 308
5 asthma->30 to 59 282
6 asthma->60+ 113
7 CHD->0 to 29 1
8 CHD->30 to 59 93
9 CHD->60+ 229
10 diabetes->0 to 29 30
11 diabetes->30 to 59 246
12 diabetes->60+ 228
13 osteoporosis->60+ 200
For another example:
SELECT CROSSJOIN([GenD].[H1].[Gender].Members,
{[Measures].[%COUNT],[Measures].[Avg Age]}) ON 0,[DiagD].[H1].[Diagnoses].Members ON 1
FROM [Patients]
Patient Coun Avg Age Patient Coun Avg Age
1 None 419 35.61 412 32.36
2 asthma 51 37.12 33 27.79
3 CHD 17 70.47 19 62.42
4 diabetes 25 63.96 21 57.67
5 osteoporosis 20 80.55 2 74.50
