none
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])

    Thanks in advance

    Friday, January 03, 2014 1:13 AM

Answers

  • 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
    FROM [Adventure Works]

    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
    FROM [Adventure Works]

    Reference: DistinctCount -- Analysis Service

    Regards,


    Charlie Liao
    TechNet Community Support

    Sunday, January 05, 2014 2:22 PM