February 1, 2011 at 12:04 pm
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!
February 1, 2011 at 8:20 pm
Since I haven't got your cube definitions, I did what I think is the equivalent in Adventureworks. My "Spe. cial exclusion" calculation excludes the color Black from its calculation. Below is what I did...hope it helps
CREATE MEMBER CURRENTCUBE.[Measures].[Special exclusion]
AS [Measures].[Internet Sales Amount],
VISIBLE = 1 ;
SCOPE ([Product].[Color].&[Black]);
([Measures].[Special exclusion]) = null;
END SCOPE;
SCOPE ([Product].[Color].[All]);
([Measures].[Special exclusion]) = SUM (EXCEPT( [Product].Color.Children, {[Product].[Color].&[Black] }) ,[Measures].[Internet Sales Amount] );
END SCOPE;
February 2, 2011 at 12:05 am
ahhhh, that looks like it should do the trick!
will let you know when i get to the office 😀
mucho thanks
February 2, 2011 at 1:41 am
just to follow up on this, yep it works a treat!
many thanks 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply