Skip to main content

CROSSJOIN (MDX)

Returns a set of tuples formed by the cross-product of the specified sets.

Returned Type

This function returns a set of tuples.

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

See Also

Next sectionCURRENTMEMBER (MDX)
Previous sectionCOUSIN (MDX)
FeedbackOpens in a new tab