FAQ: MDX Crossjoin()

This is a post to address one of the most frequently asked MDX related questions or issues. Someone will ask: why isn't this CrossJoin function working?

CrossJoin({[Actual],[Budget]},{[Account_123]},{[Jan]})

The reason the above won't work is that the function is being fed three arguments and the CrossJoin function will only accept two. The CrossJoin function will take two sets and expand them into all possible combinations of tuples. In order to expand more than two dimensions, one needs to use more than one CrossJoin. The above can be re-written correctly as:

CrossJoin(
    CrossJoin({[Actual],[Budget]},{[Account_123]}),
    {[Jan],[Feb]})

And will yield a set containing the following tuples:

([Actual],[Account_123],[Jan])
([Actual],[Account_123],[Feb])
([Budget],[Account_123],[Jan])
([Budget],[Account_123],[Feb])

You can nest as many CrossJoins as you like, adding one additional function for each new dimension you wish to expand.

On a related note, I also see people asking a similar question that I'd give a slightly different answer to. Why isn't this CrossJoin function working?

CrossJoin({[Actual]},{[Account_123]},{[Jan]})

The same answer still applies here that too many arguments are being sent to the CrossJoin function. However, in this case, no CrossJoin function is needed as there is only a single member from each dimension. Simply using a tuple here will be sufficient. That would be written as follows:

([Actual],[Account_123],[Jan])