Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MDX to exclude and filter sets Expand / Collapse
Author
Message
Posted Sunday, December 01, 2013 10:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 31, 2014 1:42 AM
Points: 4, Visits: 24
For several days, I've been trying to write mdx select correctly, but the result I get every time is either more than the right number or an error.
I have to count my orders but exclude orders with Customer Flag 1 in Year 2010.
Basically, I have a Fact table with Orders + DIM Customer + DIM Date. Than I have:
WITH MEMBER [count] as count(exists([DIM ORDERS].[ORDERS ID].children,
{EXCEPT([DIM CUSTOMER].[FLAG].children,FILTER([DIM CUSTOMER].[FLAG].&[1] ,[DIM CUSTOMER].[FLAG].currentmember IS [DIM_DATE].[YEAR].&[2010]))},
'FACT_ORDERS'
))

SELECT [count] on 0
from [Cube]


Where am I going wrong? Is this a correct MDX for my task. Please help me.
Post #1518706
Posted Tuesday, December 03, 2013 7:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 18, 2014 3:43 AM
Points: 390, Visits: 701
I had a similar thing once where I had to so a sum of a count only for an unknown year and excluding certain statuses.
I did this
SUM({(FILTER([Repair Date].[Year].MEMBERS,[Repair Date].[Year].CURRENTMEMBER.NAME = 'Unknown'),
EXCEPT([Status].[Status].[Status], {[Status].[Status].&[Closed],[Status].[Status].&[Deleted]}))}
,[Measures].[My Measure])

Hope that helps





I'm on LinkedIn
Post #1519216
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse