Anyone decent with MDX?

[BGM]

Hi, This has been driving me nuts for the best part of an hour.. please help, my MDX is clearly far too rusty :/

I have a Fact table with say 10 records.., and a Dimension table with 2 options in it

I have a Fact Count Measure called 'Count', which results in the following:

           Count
Option 1     3
Option 2     7
---------------------------
Total        10

The requirement is to show a count of everything that is NOT Option 1, and so originally i did this:

create member currentcube.[measures].[myNewMeasure]
as
	([measures].[count], [dim].[dim option].[Option 2])
,visible=1;

but that just results in:

           Count     myNewMeasure
Option 1     3          7
Option 2     7          7
---------------------------
Total        10         7

which i am not especially happy with, not to mention it not supporting addition of any other members to the dimension in future

so, here lies my troubles.. what i think i really want to achieve is the following:

           myNewMeasure
Option 1     
Option 2     7
---------------------------
Total        7

and to do this i have been messing around with EXCEPT() and IIF() and CASE etc... all to no avail

can someone please put me out of my misery and point me to how to achieve what i want, so that ultimately i can get to something:

           myNewMeasure
Option 1     
Option 2     7
Option 3     3
Option 4     8
---------------------------
Total        18

I am thinking scope statement to null off where the member is "Option 1"? But I don't think you can do those on a calculated measure??

Thanks!

 

[NeoMatriX724]

I haven't written MDX in ages, but why wouldn't you use NOT?

 

[BGM]

I haven't written MDX in ages, but why wouldn't you use NOT?

heya,

i tried messing around with NOT() also, but couldn't seem to get it to do what i wanted.. i was probably using it incorrectly

i've slept on it and i'm going to give a couple of things a twirl today.. the first being scope statements just to null off the level i am not interested in, and the other something like

iif(except([dimension].[all],[dimension].[opt1])
      ,<< do this >>
      ,<< else this >>
)

not sure if that will work, but gonna try it anyway