Search code examples
ssasmdxmdx-query

MDX query to count number of product names

I am new to MDX and trying to count number of product names on a week basis. I have written the below MDX query, but I get all product count as a result.

WITH MEMBER [Measures].[Count Of Members] AS
Count(Existing [Product Dimension].[ProductName].Members)

SELECT {[Measures].[Count Of Members]} ON 0,  
NON EMPTY
{
[Date Dimension].[Financial WC]
} ON 1

FROM [Cube]
WHERE ([Date Dimension].[FinancialYear].&[2018/19],
       [Sales].[PaymentType].&[Delivery])

I am getting result as :

"All 230"

as there are total 230 product names

But I want the result as:

week1 50 week2 73 week3 34 . .

Solution

  • Welcome to MDX. Your quest has an issue. When you say you want to count "product names on a week basis" you will need to count them on the basis on a Fact. So for example the count of the products that were sold in that week. The way you are counting is basicly going to count all the products in your product dimension.

    For the below sample i am using adventure works.

    In the below sample i want to know how many type of products were sold in week 1. Here we are talking about count of product type not units. The query below does that.

    select [Measures].[Internet Sales Amount] on columns,
non empty
([Date].[Calendar Week of Year].&[1],[Product].[Product].[Product])
on rows 
from [Adventure Works]

    Result:enter image description here

    Result Countenter image description here

    Notics in the result count it shows 97 rows. this means 96 products were sold,the result also includes the column name hence the count is 97.

    Now lets take a look at the query that solves your issue.

    with member
[Measures].[Test]
 as 
count(
filter
(([Date].[Calendar Week of Year].currentmember, [Product].[Product].[Product]),[Measures].[Internet Sales Amount]>0)
)
select 
[Measures].[Test]
on columns,
[Date].[Calendar Week of Year].[Calendar Week of Year]
on rows 
from [Adventure Works]

    Result

    enter image description here

    So this shows that 96 products were sold.

    The logic behind the query is we count all the products that has >0 sales for a week. ".currentmember" transalates to the current week, hence we can calulate for any week.