For this typical requirement, Marco Russo mentioned a solution in his great Analysis Services Advanced Best Practices presentation – writing the following sales_amount_range business logic in view for a new cube dimension to distinct count salesman:

CASE
WHEN sales_amount IS NULL OR sales_amount < 0 THEN 'N/A'
WHEN sales_amount < 100 THEN '<$100'
WHEN sales_amount <= 500 THEN '$500'
END

Now let’s look at the more complex requirement – what if the desired result looks as follows:

Because the distinct_count_of_salesman aggregate can be based on correlations salesman dimension with dynamic date dimension ranges, which makes pre-aggregation impossible, we have to resort to MDX solution.

More than 4 years ago when I worked on a business intelligence project, I had such a requirement and I asked for help on MSDN forum. Tomislav Piasevoli and Richard Less very kindly helped. Thanks Tomislav and Richard! So I copied Tomislav’s MDX solution as follows:

One thing I’d like to quote from what Tomislav said in the MSDN forum thread is “In general, having a simple MDX is an indicator of good DW design. Having a complex MDX might be a good sign to reconsider your DW design. Or to use different approach, even technology sometimes.” Therefore, if there are possible efficient solution inside the cube, MDX solution will be much less preferred.