Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Crossjoin function now takes more than two parameters

with 3 comments

Crossjoin is a function which, for obvious reasons, is dear to my heart. It’s been at least a year since I last found a feature in AS2005 MDX that I wasn’t aware of, but the other day I found out that the Crossjoin function can now take more than two sets as parameters, much in the same way that NonEmptyCrossjoin can. Here’s an example:
 
select
measures.[internet sales amount] on 0,
crossjoin(
[Date].[Day Name].[Day Name].members,
[Date].[Day of Week].[Day of Week].members,
[Product].[Color].[Color].members
)
on 1
from [Adventure Works]
 
Not exactly revolutionary (and anyway I use the * operator instead nowadays) but fun to note.

Written by Chris Webb

February 2, 2007 at 11:36 am

Posted in MDX

3 Responses

Subscribe to comments with RSS.

  1. Funny you post this, just as I was finishing my new blog about Crossjoin. Well, let\’s test if you really know this function and whether you discovered all the features of AS – here is a little quiz question: Do you know that there are 4 different ways in AS to compose a Crossjoin (you are obviously familiar with 2 ways, but what about other 2). I will hold posting my blog until you give an answer. :)

    Mosha

    February 2, 2007 at 3:48 pm

  2. Oooh, I love a challenge! Here\’s #3, putting the sets inside a tuple:
     
    selectmeasures.[internet sales amount] on 0,([Date].[Day Name].[Day Name].members,[Date].[Day of Week].[Day of Week].members,[Product].[Color].[Color].members)on 1from [Adventure Works]
    …although I hate it, I\’ve seen too many people confused by doing this. As for #4, well I could have sworn there was an alternative function which was in the OLEDB for OLAP spec which did exactly the same thing as Crossjoin; I tried Cross() but that doesn\’t work, but I\’m sure it\’s something like that. Go on, put me out of my misery…
     
    I could have come out with other answers like using NonEmptyCrossjoin or using Generate() but that seemed a bit like cheating.

    Chris

    February 2, 2007 at 9:41 pm

  3. You #3 is a good answer, but I don\’t know why you called it a "tuple". It is a set, not a tuple.
    You cannot simulate CrossJoin using NonEmptyCrossJoin – so it doesn\’t count. Using Generate would require to put something as generator, and you are back to the original question – how can you alias CrossJoin function. As soon as I have more time to finish my blog you will know about #4.

    Mosha

    February 2, 2007 at 10:35 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,859 other followers

%d bloggers like this: