Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate SQL, Power BI, Fabric, and AI community-led event. March 31 - April 2. Use code MSCUST for a $150 discount. Prices go up Feb 11th.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Returns the cross product of one or more sets.
Standard syntax
Crossjoin(Set_Expression1 ,Set_Expression2 [,...n] )
Alternate syntax
Set_Expression1 * Set_Expression2 [* ...n]
Set_Expression1
A valid Multidimensional Expressions (MDX) expression that returns a set.
Set_Expression2
A valid Multidimensional Expressions (MDX) expression that returns a set.
The Crossjoin function returns the cross product of two or more specified sets. The order of tuples in the resulting set depends on the order of the sets to be joined and the order of their members. For example, when the first set consists of {x1, x2,...,xn}, and the second set consists of {y1, y2, ..., yn}, the cross product of these sets is:
{(x1, y1), (x1, y2),...,(x1, yn), (x2, y1), (x2, y2),...,
(x2, yn),..., (xn, y1), (xn, y2),..., (xn, yn)}
Important
If the sets in the cross join are composed of tuples from different attribute hierarchies in the same dimension, this function will return only those tuples that actually exist. For more information, see Key Concepts in MDX (Analysis Services).
The following query shows simple examples of the use of the Crossjoin function on the Columns and Rows axis of a query:
SELECT
[Customer].[Country].Members *
[Customer].[State-Province].Members
ON 0,
Crossjoin(
[Date].[Calendar Year].Members,
[Product].[Category].[Category].Members)
ON 1
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]
The following example shows the automatic filtering that takes place when different hierarchies from the same dimension are crossjoined:
SELECT
Measures.[Internet Sales Amount]
ON 0,
//Only the dates in Calendar Years 2003 and 2004 will be returned here
Crossjoin(
{[Date].[Calendar Year].&[2003], [Date].[Calendar Year].&[2004]},
[Date].[Date].[Date].Members)
ON 1
FROM [Adventure Works]
The following three examples return the same results - the Internet Sales Amount by state for states within the United States. The first two use the two cross join syntaxes and the third demonstrates the use of the WHERE clause to return the same information.
SELECT CROSSJOIN
(
{[Customer].[Country].[United States]},
[Customer].[State-Province].Members
) ON 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]
SELECT
[Customer].[Country].[United States] *
[Customer].[State-Province].Members
ON 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]
SELECT
[Customer].[State-Province].Members
ON 0
FROM [Adventure Works]
WHERE (Measures.[Internet Sales Amount],
[Customer].[Country].[United States])
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate SQL, Power BI, Fabric, and AI community-led event. March 31 - April 2. Use code MSCUST for a $150 discount. Prices go up Feb 11th.
Register today