CODEWITHSUNDEEP
ssasmdxolap
jlj
jlj

Reputation: 85

mdx topcount results not as expected

When I run the query below I only get 2 records back. If I drop the TOPCOUNT, I get 508 records.

Why is it not giving me the top 5 from the 508 records? What am I missing?

with 
MEMBER Measures.[EmailCount] as IIF(ISEMPTY([Measures].[Tran Count]), 0 ,[Measures].[Tran Count])  
MEMBER Measures.[IncomePerEmail] as 
    [Measures].[Amount]/ IIF(Measures.[EmailCount] = 0, 1 , Measures.[EmailCount] )
MEMBER Measures.[Income Range] as
 CASE  
      WHEN Sum(EXISTING [Dim IFA Details].[Parent Key].[Adviser Group].Members,
            Measures.[Amount] ) <= 10000 THEN  '0-10000'
      WHEN Sum(EXISTING [Dim IFA Details].[Parent Key].[Adviser Group].Members,
            Measures.[Amount] ) <= 50000 THEN  '10001-50000'
      WHEN Sum(EXISTING [Dim IFA Details].[Parent Key].[Adviser Group].Members,
            Measures.[Amount] ) <= 100000 THEN  '50001-100000'
      WHEN Sum(EXISTING [Dim IFA Details].[Parent Key].[Adviser Group].Members,
            Measures.[Amount] ) <= 200000 THEN  '100001-200000'
      else '> 200000'
      end

SELECT   {  [Measures].[Amount] , Measures.[EmailCount], measures.[Income Range], Measures.[IncomePerEmail] } 
 ON COLUMNS, 
 TOPCOUNT( 
 NONEMPTY([Dim IFA Details].[Parent Key].[Adviser Group].Members, Measures.Amount)
, 5
, Measures.[IncomePerEmail] 
)
having Measures.[Income Range] = '10001-50000' 
on rows
FROM [Income and Emails Cube] 
where [Dim Date].[Fiscal Year].&[FY 13/14]

Upvotes: 0

Views: 167

Answers (1)

SouravA
SouravA

Reputation: 5243

You have a HAVING clause which basically filters the set(of 5 records).

TOPCOUNTgets you 5 members but when the filtering happens, only two of the members meet the criteria Measures.[Income Range] = '10001-50000'

If you want to verify this, ORDER the query based on Measures.[IncomePerEmail] and see if the "top 5 records" have income ranges as '10001-50000'

Upvotes: 1

Related Questions