Search code examples
mdx

Distinct Count of products for each year

I checked a lot of others resources before asking but I didn't find anything for me.

I'm sorry if my question is stupid or too simple but I tried to learn MDX from today for an exam.

I have a cube with sales and products.

I try to find the number of distinct products orderd each year.

    WITH MEMBER [produitDistinct] AS 
            DISTINCTCOUNT([Produits].[Marque - Nom - Variante].members)
SELECT [produitDistinct] ON COLUMNS,
[Date commande].[Année - Mois - Jour].[Année].MEMBERS ON ROWS
FROM [Cube ventes];

In English, would be something like

WITH MEMBER [productDistinct] AS 
        DISTINCTCOUNT([Products].[Brand - Name - Variant].members)
SELECT [productDistinct] ON COLUMNS,
[Date order].[Year - Month - Day].[Year].MEMBERS ON ROWS
FROM [sales cube];

Unfortunately, the result is similar (and wrong!) For each year. I get 20537 for each year (correct answer is supposed to be 18'255)

Result:
Result

Do you have any idea of what could be wrong?

Solution

  • When you say you want to have a distinct count of products that were sold each year, you need to filter products that have sales against them, then count them. Your query on the other hand is counting the Dimension-Attribute members, which will remain the same, no matter if they were sold or not.

    Take a look at the below answer i gave to another question. You go through the entire example or you can go directly to the last query. It is what you want to achieve.

    MDX query to count number of product names

    Lastly best of luck for your exam.