Filter MDX measure by dimension members

0
votes

Let's say I have a dimension of customers, and I'd like to get all those customers, who generated revenue greater than 100... How do you do that in MDX? I have created a member, using a filter such as this

Filter([Dim Customer].[Id].Members, [Measures].[Revenue] > 150)

However it's not working as expected. How do you usually go around this? I'd like to see 1 row, with 2 columns, all revenue and the revenue from customers that generated more than 150. My problem is that since I don't want the rows of customer to be displayed on the rows axis - it is not considering the filter per customer, but on the whole [Measures].[Revenue].

2
mdx

2 Answers

1
votes

Remember that the Filter()-function returns a set, namely the set of all customers with a revenue over 150. You then need to specify that you want to aggregate something over this set. This means your calculated measure should like this:

WITH MEMBER [Measures].[Revenue for Customers over 150] AS
    Aggregate(
        Filter(Existing ([Dim Customer].[Id].Members),
            [Measures].[Revenue] > 150),
        [Measures].[Revenue]
    )
0
votes

the filter() function should do:

SELECT [Measures].[Internet Sales Amount] ON 0,
filter(
[Customer].[Customer].[Customer].MEMBERS
, [Measures].[Internet Sales Amount] > 150
) ON 1 
FROM [Adventure Works]

About half of the customers are filtered out.

Philip,