# How to get sum distinct in MDX

### Question

• I have the following sample data:

InvoiceId   Amount
1          10
1          10
2          20

Hot to define calculated member that produce the correct result, i.e 30, skipping duplicate rows based on the InvoiceId? I created dimension based on my fact table (used as measure for amount also).

I have tried with the following MDX expression but unfortunately, all members are taken dint sum (i.e. get 40 as result).
SUM(DISTINCT([Dim Fact Invoice].[InvoiceId].[ InvoiceId]), [Measures].[ Amount])

Friday, January 03, 2014 1:13 AM

• Hi Sanjin,

There are number of articles on how to optimize distinct count. In general there we can get distinct count by two ways. Set aggregation usage of Distinctcount type while designing cube and using DistinctCount MDX Function. And here is a sample query for your referece.

```WITH MEMBER [measures].[AlternateDistinctCount]
AS
COUNT( nonempty([Product].[Product].MEMBERS,[Measures].[Reseller Order Count]))
MEMBER [measures].[MDXDistinctCount]
AS
DISTINCTCOUNT({[Product].[Product].members*[Measures].[Reseller Order Count]})
SELECT {[measures].[AlternateDistinctCount],[measures].[MDXDistinctCount]} ON 0,
[Reseller].[Reseller].MEMBERS
ON 1

Reference: DistinctCount -- Analysis Service

Regards,

Charlie Liao
TechNet Community Support

Sunday, January 05, 2014 2:22 PM

### All replies

• depending on the size of your fact, i would try to either create a distinct measure fact or add another column on the current fact with 0 on duplicate.

Friday, January 03, 2014 4:19 PM
• Hi Sanjin,

There are number of articles on how to optimize distinct count. In general there we can get distinct count by two ways. Set aggregation usage of Distinctcount type while designing cube and using DistinctCount MDX Function. And here is a sample query for your referece.

```WITH MEMBER [measures].[AlternateDistinctCount]
AS
COUNT( nonempty([Product].[Product].MEMBERS,[Measures].[Reseller Order Count]))
MEMBER [measures].[MDXDistinctCount]
AS
DISTINCTCOUNT({[Product].[Product].members*[Measures].[Reseller Order Count]})
SELECT {[measures].[AlternateDistinctCount],[measures].[MDXDistinctCount]} ON 0,
[Reseller].[Reseller].MEMBERS
ON 1