MDX: Filtering a member set by a measure's table values

Question

I have some numbers in a fact table, and have generated a measure which use the SUM aggregator to summarize the numbers. But the problem is that I only want to sum the numbers that are higher than, say 10. I tried using a generic expression in the measure definition, and that works of course, but the problem is that I need to be able to dynamically set that value, because it's not always 10, meaning users should be able to select it themselves.

More specifically, my current MDX looks like this:

WITH
SET [Email Measures] AS '{[Measures].[Number Of Answered Cases], 
[Measures].[Max Expedition Time First In Case], [Measures].[Avg Expedition Times First In Case], 
[Measures].[Number Of Incoming Email Requests], [Measures].[Avg Number Of Emails In Cases],
[Measures].[Avg Expedition Times Total],[Measures].[Number Of Answered Incoming Emails]}' 

SET [Organizations] AS '{[Organization.Id].[860]}' 
SET [Operators] AS '{[Operator.Id].[3379],[Operator.Id].[3181]}'
SET [Email Accounts] AS '{[Email Account.Id].[6]}'
MEMBER [Time.Date].[Date Period] AS Aggregate ({[Time.Date].[2008].[11].[11] :[Time.Date].[2009].[1].[2] }) 
MEMBER [Email.Type].[Email Types] AS Aggregate ({[Email.Type].[0]}) 
SELECT {[Email Measures]} ON columns,
[Operators] ON rows 
FROM [Email_Fact]
WHERE ( [Time.Date].[Date Period] )

Now, the member in question is the calculated member [Avg Expedition Times Total]. This member takes in two measures; [Sum Expedition Times] and [Nr of Expedition Times] and splits one on the other to get the average, all this presently works. However, I want [Sum Expedition Times] to only summarize values over or under a parameter of my/the user's wish.

How do I filter the numbers [Sum Expedition Times] iterates through, rather than filtering on the sum that the measure gives me in the end?

Answer 1

You could move the member into the MDX query, instead of putting it in the cube. Then you get something like....

WITH
MEMBER [Avg Expedition Times Total] AS
SUM(
    FILTER([Your Dimension],
        [Measure you want to filter] > 10),
    [Measure you want to sum])

I'm not sure exactly which dimensions and measure you want to filter and sum by, but I think this is a step in the right direction. If your users can't modify the MDX (or don't want to!) then creating multiple measures is a pretty solid solution too.

Answer 2

ALL THIS MEMBERS Returns the same results, I'm trying to limit the numbers to summarize without success. PLEASEE HELP ME

WITH MEMBER [Measures].[Ventas Mayores 7000] as (iif([Measures].[Internet Sales Amount]>7000, [Measures].[Internet Sales Amount],0)) MEMBER [Measures].[TEST] AS SUM( FILTER( EXCEPT([Date].[Month of Year].ALLMEMBERS, [Date].[Month of Year].[All]) , [Measures].[Internet Sales Amount]>7000), [Measures].[Internet Sales Amount]) select ([Date].[Month of Year] ) on rows, {[Measures].[Internet Sales Amount] ,[Measures].[Ventas Mayores 7000], [Measures].[TEST]} on columns FROM [Adventure Works]