MDX - Count function not returning correct numbers

Question

Hi,

I am trying to get number of months having Internet sales amount < 1500000 from Adventure works cube,

MDX1 :: select Filter(([Measures].[Internet Sales Amount],[Date].[Calendar].[Calendar Quarter]),[Measures].[Internet Sales Amount]<1500000) on 0 from [Adventure Works];

MDX1 gives me result as below, which means every year have one month only having  Internet sales amount < 1500000

MDX2 :: with member [No of Months having Internet Sales Amount < 1500000] as
count(Filter(([Measures].[Internet Sales Amount],[Date].[Calendar].[Calendar Quarter]),[Measures].[Internet Sales Amount]<1500000),excludeempty)
select [No of Months having Internet Sales Amount < 1500000] on 0 from [Adventure Works];

MDX2 give me 5 as result.

MDX3 ::with member [No of Months having Internet Sales Amount < 1500000] as
count(Filter(([Measures].[Internet Sales Amount],[Date].[Calendar].[Calendar Quarter]),[Measures].[Internet Sales Amount]<1500000),excludeempty)
select [No of Months having Internet Sales Amount < 1500000] on 0,[Date].[Calendar].[Calendar Year].members on 1 from [Adventure Works];

MDX3 returns me all year with same count as 5 which should be 1 actually. Can someone help me out getting the desired result.

Thanks in advance !!

Answer 1

Here you have query, which gives numer of months having Internet saless less than 1500000. It Lists all the periots up to the Month Level, so it is easy to verify the correctness.

WITH
	MEMBER [Months Having Less Than 1500000] AS COUNT(FILTER(EXISTING [Date].[Calendar].[Month].MEMBERS, [Measures].[Internet Sales Amount] < 1500000)) 
SELECT
	{
		[Measures].[Internet Sales Amount],
		[Months Having Less Than 1500000]
	} ON COLUMNS,

	{
		DESCENDANTS([Date].[Calendar].[All Periods], [Date].[Calendar].[Month], SELF_AND_BEFORE)
	} ON ROWS
FROM [Adventure Works]

It is Important to use EXISTING keyword inside the Filter so only members belonging to current processed scope are taken in mind.

Answer 2

Excellent … exactly what I want. Thank You !!