MDX with multiple NOT In Condition

Question

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 ??

Answer 1

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>>
))))

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 2

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>>

Answer 3

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,

Answer 4

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).

Answer 5

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 .??

Answer 6

Hi MDX,

Thanks for the Reply.

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

Answer 7

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.

Answer 8

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.

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 9

Hi Michael,

Thanks that solves my Problem :)

Answer 10

No Problem - please just mark them as helpful or as the answer.