Hi,

I have 2 dimensions, product and broker and a measure let's say
Profit.

I've created an mdx statement that will return the top 5 products and
then within that return the top 5 brokers within those top 5 products.

Here's the statement:

SELECT
{[Measures].[Profit]} ON COLUMNS,
GENERATE( TopCount( [Product].[Label].MEMBERS, 5,
[Measures].[Profit]), {Product.CurrentMember} * TopCount(
[Broker].[Agent Name].MEMBERS, 5, [Measures].[Profit]) ) )

ON ROWS
FROM [Sales]

This works, except that some products do not join with at least 5
brokers, so I'm getting only 2 or 3 top brokers within the products.
Is it possible to further filter the statement so that it ensures that
only products with at least 5 brokers are displayed?

Thanks in advance,
Clem

If you want to filter the initial Top 5 product set:

{[Measures].[Profit]} ON COLUMNS,
GENERATE(Filter(TopCount([Product].[Label].MEMBERS, 5,
[Measures].[Profit]),
NonEmptyCrossJoin([Broker].[Agent Name].MEMBERS,
{Product.CurrentMember}, 1).Count >= 5),
{Product.CurrentMember} * TopCount(
[Broker].[Agent Name].MEMBERS, 5, [Measures].[Profit]))

ON ROWS
FROM [Sales]

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!