MDX: Filtering a member set by a measure's table values
- by oyvinro
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?
