MDX with multiple NOT In Condition

# MDX with multiple NOT In Condition

• Monday, April 16, 2012 10:31 AM

I Experts ,

I am Working On SSAS 2008,
I have 4 Dimension in my Cube ie 1)DimColor 2)DimStatus  3)DimCity 4)DimProduct

DimColor          DimStatus         DimCity                         DimProduct
Color                  Status            City                               Product
White                  Active              London                          Prod1
Black                   InActive           HongKong                     Prod2
Red                    Success             Singapore                    Prod3

I have a Default Measure "[Measure].[Revenue]".
I need to find Revenue where
Color=Black and
Status    Not Equal to Active and
City        Not Equal to London and
Product Not Equal to  Prod2

I need to create Calculated Member in my Cube :
I am aware of "Except"  Function,But how to use in this scenario
If any One could help me with the syntex ??

• Edited by Monday, April 16, 2012 10:32 AM
•

### All Replies

• Monday, April 16, 2012 11:57 AM

Hi Mushtaq,

Try this

```Select [Measure].[Revenue] on o,
<<something on rows>> on 1
FROM (SELECT ([Color].[Color].&[Black]) on 0
FROM (SELECT -([Status].[Status].&[Active]) on 0
FROM (SELECT -([City].[City].&[London]) on 0
FROM (SELECT -([Product].[Product].&[Prod2]) on 0
FROM <<cube name>>
))))```

Cheers, Raunak | t: @raunakjhawar | My Blog

• Monday, April 16, 2012 12:57 PM

Except takes two sets, and will provide you with values in the first that don't exist in the second. If you have duplicates in the first that you want to retain, then you need to specify the ALL as a third parameter.

So you could say

SELECT [Measures].[Revenue] on 0

,{[Color].[Color].&[Black]} * EXCEPT([Status].[Status].[Status], [Status].[Status].&[Active])

* {EXCEPT([City].[City].[City], [City].[City].&[London])}

* {EXCEPT([Product].[Product].[Product], [Product].[Product].&[Prod2])} ON 0

FROM <<Cube Name>>

• Monday, April 16, 2012 2:21 PM

hi,

A last option!  replace:  {[Dim].[Main Dimesion Attribute].[attribute].members} by

{exists([Dim].[Main Dimesion Attribute].[Main Dimesion Attribute].members,[Dim].[Main Dimesion Attribute].[Discriminating Attribute].[All "Dimesion Attribute"s])}
-
{exists([Dim].[Main Dimesion Attribute].[Main Dimesion Attribute].members,[Dim].[Main Dimesion Attribute].&[excluded Discriminating member])}

in case of color:  replace: [color].[color].[color].members

by:

{exists([color].[color].[color].members,[color].[color].[All Colors])}
-
{exists([color].[color].[color].members,[color].[color].&[Black])}

You can drop the exist function when used in the WHERE clause and in some calculations.  You won't be able to use visual totals as it is possible in a sub select.

Philip,

• Monday, April 16, 2012 3:21 PM

Hi Michael,

I tried Using the Query proposed By you in my Cube .
I have use exactly the same syntex in my Cube ,but I get error that"Syntex for Select is Incorrect"

wITH  Member Measures. x as
SELECT [Measures].[Revenue] on 0

,{[City].[City].&[City2]}
* EXCEPT([Color].[Color].[Color],[Color].[Color].&[Black])

* {EXCEPT([Product].[Product].[Color],[Product].[Product].&[Prod2])}

* {EXCEPT([States].[States].[Color],[States].[States].&[State2] )} ON 1

FROM [Cube_NotIN]

Note: I want to create Calculated Member ,and Calculated Member in cube does not accept Select clause(If  I am not wrong).

• Monday, April 16, 2012 3:26 PM

Hi Raunak,

Thanks for Reply.but While creating Calculated Member in Cube it does not accept "Select " or Could say we cant use the same MDX Query Mentioned by u  to create Calculated Member.?

Hope I made u clear What I mean by above line .??

• Monday, April 16, 2012 3:28 PM

Hi MDX,

But I am confused as how to use your approach(MDX syntex) in my Scenario.

• Monday, April 16, 2012 3:29 PM

You are correct that Member does not accept SELECT in it. Are you trying to get a total for all the measures, or just a list of the revenue for each measure?

If you want the sum of Revenue for the explicit ommissions, then you will want to SUM those sets so

WITH MEMBER [Measures].[X] AS

SUM(,{[City].[City].&[City2]}
* EXCEPT([Color].[Color].[Color],[Color].[Color].&[Black])

* {EXCEPT([Product].[Product].[Color],[Product].[Product].&[Prod2])}

* {EXCEPT([States].[States].[Color],[States].[States].&[State2] )} , [Measures].[Revenue])

SELECT [Measures].[X] ON 0

FROM [Cube_NotIN]

If you want teh data to be at the individual levels then you don't need the calculated member at all.

• Monday, April 16, 2012 3:36 PM

Hi Mushtaq,

Do you any measure in your cube by name: Revenue?

if yes, there is no need to create another calculated member. Just use the systax I have used and let us know the outcome.

Cheers, Raunak | t: @raunakjhawar | My Blog

• Monday, April 16, 2012 3:38 PM

Hi Michael,

Thanks that solves my Problem :)

• Monday, April 16, 2012 3:40 PM