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.
It looks like I have misunderstood question. In this case solution would be:
WITH MEMBER [YourCount] AS
IIF( [DIM_DATE].[YEAR].CURRENTMEMBER IS [DIM_DATE].[YEAR].&[2010],
IIF( NOT([DIM CUSTOMER].[FLAG].CURRENTMEMBER IS [DIM CUSTOMER].[FLAG].&[1]),
[Measures].[YourMeasureOnOrder],
NULL)
),
[Measures].[YourMeasureOnOrder])
SELECT
NON EMPTY {[YourCount]} ON 0
FROM [Cube]