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?

I realize now that I may not be able to do what I want to do directly through MDX, since it operates on a higher level than I need it to. I've solved it by creating several measures in the cube that are used separately according to the user's input. This way MDX only controls which measure to use. — oyvinro

Dave Bauman Dave Bauman · Accepted Answer · 2009-04-22T06:49:47

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.

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

2 Answers