0

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?

MoazRub
  • 2,881
  • 2
  • 10
  • 20
Roman
  • 23
  • 4

1 Answers1

0

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.

MoazRub
  • 2,881
  • 2
  • 10
  • 20
  • Thank you, that worked perfectly! I have another question about something I don't understand in your answer. Why doesn''t it make any difference whether I use COUNT or DISTINCTCOUNT ? Both are giving same result (distinct). – Roman Jan 20 '19 at 09:27
  • @Roman you are welcome, count and distcount both work is that we are first determining the products that have sales against them. Next we count them. Now you need to be clear that we did not find the units of products, we found the generic products that had sales.So lets consider if the data is for a simple grocery store and in Jan 2019 we sold 100 eggs and 50 bottles of milk. The filter in my query will just fetch Milk and eggs since they have sales. the remaining products are not fetched. Hence no point using distinct count. As milk was fetched not units of milf sold. – MoazRub Jan 20 '19 at 10:59