zahid's BI blog

MDX cross join function returns the cross product of two sets. For example, we have two sets A = {1,2} and B = {x,y}. After crossjoin, we get, A X B = {(1,x),(1,y),(2,x),(2,y)}.

(1,x), (1,y) are individual tuples. The resultant set consists of these tuples.

Now, let’s see the below mdx query which uses crossjoin to form a named set called ASet. Crossjoin is done between a particular member of category hierarchy (“Hair Care”) and sub-categories hierarchy. A measure called [SetToString] is decalred. It calls SetToStr function on the named set. So, all the members of the set are converted into string.

with
set ASet
as
{crossjoin([Dim Category].[Category].[Category].&[Hair Care],[Dim Category].[Sub Category].[Sub Category])}
member [Measures].[SetToString]
as
SetToStr(ASet)
select ASet
on 1,
{[Measures].[SetToString]}
on 0
from [MY_CUBE]

returns result

A close look at the returned measure
{
( [Dim Category].[Category].&[Hair Care], [Dim Category].[Sub Category].&[Conditioner] ), <————-tuple
( [Dim Category].[Category].&[Hair Care], [Dim Category].[Sub Category].&[Shampoo] )      <————–tuple
}