none
Please help me !!! MDX Count distinct script

    Question

  • I need your help.

    I have a cube with 3 dimensions

    - DimProduct with 3 hierarchies
             - Category 
             - Sub Category
             - Product

    - DimClients with 1 hierarchy

    - DimDate with 1 hierarchy

    And 1 Measure : Sales Amount

    I want to display for a rapport 2 news measures:
     - The number of product saled by Year by Clients
     - The number Total of product 


    This is what I want to display:

    Category,  SubCategory, Product, Date, Client, SalesAmount, Number Product saled, Number Total Product

    Please can U help me to write this mdx query ????

     
    Sunday, July 28, 2013 2:24 PM

Answers

  • Hi Lidou123,

    We can create two measures with Distinct Count usage. For more information, please see:
     Create an Internet Distinct Count Sales Order measure in the Internet Sales table: http://technet.microsoft.com/en-us/library/hh231688.aspx

    Please also refer to the following article to organize the MDX query
     DistinctCount (MDX):  http://technet.microsoft.com/en-us/library/ms145519.aspx

    Adventure works sample code:
    WITH SET MySet AS         

    {[Customer].[Customer Geography].[Country].&[Australia],[Customer].[Customer Geography].[Country].&[Australia],         

    [Customer].[Customer Geography].[Country].&[Canada],[Customer].[Customer Geography].[Country].&[France],         

    [Customer].[Customer Geography].[Country].&[United Kingdom],[Customer].[Customer Geography].[Country].&[United Kingdom]}         

    *         

    {([Date].[Calendar].[Date].&[20010701],[Measures].[Internet Sales Amount] )}         

    //Returns the value 3 because Internet Sales Amount is null         

    //for the UK on the date specified         

    MEMBER MEASURES.SETDISTINCTCOUNT AS         

    DISTINCTCOUNT(MySet)         

    SELECT {MEASURES.SETDISTINCTCOUNT} ON 0         

    FROM [Adventure Works]

    Best Regards,


    Elvis Long
    TechNet Community Support

    Wednesday, July 31, 2013 11:31 AM