Search code examples
ssasmdxcube

MDX select to exclude filtered sets


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.


Solution

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