How to count distinct members and keep good performance

Hi all,

I have a simple cube with only one measure : "Amount", I have three dimensions Supplier, Category and Company.

I would like to display a matrix with Categories as rows, company as columns, the amount and the number of distinct suppliers who appaers for each category/company.

I tried to create a calculated measure like this:

Count(Crossjoin({[D Supplier].[Supplier Code].members}, {[Measures].[Amount]}), EXCLUDEEMPTY)

The result is OK, but when I add this calculated Measure to my query the execution time jumps from a few seconds (<10) to a few minutes. Am I missing something ?

Anyone have an idea to do this in a better way ?

On-the-fly distinct counting will be always slow. Any reason not to implement a distinct count on a separate measure group?|||

Indeed adding a separate measure group did the trick.

Thanks.

|||

Now I have another problem, for aggregating the Distinct Count, in the Cube browser it works properly, but in SSRS in a matrix it doesn't. As the query returns flat result, if I Sum the distinct count measure I have more suppliers than I should in the totals, the matrix just sum the values from the query.

How can I keep my Distinct Count working in a matrix ?

Is there any way to create the same measure but without the link to a particular dimension. What I have in mind is returning two columns for my distinctcount, one with the detailed count, one for the aggregated total by category. But I don't see how I can achieve this. Any idea ?

|||Create a calculated measure that includes the distinct count measure and the ALL member of all dimensions included in the query.|||Try using the Aggregate() function in the matrix instead of Sum() as exlained in more details here.|||Thanks a lot that solved my problem.