How to do a MDX Except crossjoin dimension query

Question

Consider the following data. I would like to get the NON{Country:USA & Gender:F} using MDX

- - - - - - - - - -  - - - - - - - - - - - 
| Country     |    Gender     |   Sales   |        
- - - - - - - - - -  - - - - - - - - - - - 
USA               M                1000
USA               F                500
Spain             M                200
Spain             F                600

What I want to extract would be:

- - - - - - - - - -  - - - - - - - - - - - 
| Country     |    Gender     |   Sales   |        
- - - - - - - - - -  - - - - - - - - - - - 
USA               M                1000
Spain             M                200
Spain             F                600

I tried to use crossjoin, union and except to do that, e.g.


    WITH SET [Test] AS
    [Country].[CountryCode].[USA] * Except ([Gender].members,{[Gender].[GenderCode].[F]}) +
    Except([Country].[CountryCode].members, {[Country].[CountryCode].[USA]}) * [Gender].members
    SELECT
       NON EMPTY [Test] ON ROWS,
       {[Measures].[Sales]} ON COLUMNS
        FROM [SalesCube]

It works, but may i know if there is any other simpler way to do it ?

Thanks.

FrankPl FrankPl · Accepted Answer · 2014-09-03T10:23:01

If you want to exclude few combinations, you can use Except on the cross join with the excepted combinations as tuples like this:

WITH SET [Test] AS
Except(CrossJoin([Country].[CountryCode].members,
                 [Gender].members
                ),
       { ([Country].[CountryCode].[USA], [Gender].[GenderCode].[F]) }
      )
...

And syntactically, you an abbreviate CrossJoin as * and Except as -, as well as Union as +, which have the usual rules of precedence (* has higher precendece than - and +):

WITH SET [Test] AS
[Country].[CountryCode].members * [Gender].[GenderCode].members
-
{ ([Country].[CountryCode].[USA], [Gender].[GenderCode].[F]) }
...

How to do a MDX Except crossjoin dimension query

1 Answers