CODEWITHSUNDEEP
mdx
NotDan
NotDan

Reputation: 32223

How to get the last, non empty 10 members with MDX?

I have an MDX query:

SELECT
NON EMPTY {Hierarchize({[Measures].[Rating]})} ON COLUMNS,
NON EMPTY {Hierarchize({[Response].[Response Name].Members})} ON ROWS
FROM [Ratings]

That returns a table of Response Names to Response count. This query returns a row for ALL responses, though, and I just want the most recent 10 responses.

I tried to do this using HEAD like so:

SELECT
NON EMPTY {Hierarchize({[Measures].[Rating]})} ON COLUMNS,
HEAD(NON EMPTY {Hierarchize({[Response].[Response Name].Members})}, 10) ON ROWS
FROM [Ratings]

but it gives an error "Syntax error at line 3, column 18, token 'NON'"

If I remove the NON EMPTY, then it works as I would expect (returns only 10 members), but then it includes the empty Ratings.

How can I use NON EMPTY and HEAD together? (Or accomplish the same thing another way)

Upvotes: 2

Views: 5375

Answers (2)

Pedro Rodrigues
Pedro Rodrigues

Reputation: 424

I believe it is already a bit late for the answer, but here's the solution I used (I was having the same problem):

select {[Measures].[Percentage Present]} ON COLUMNS, 
Head(Filter([Student].[Student].AllMembers, not isEmpty([Measures].[Percentage Present])),10) ON ROWS 
from [Attendance]

Head + Filter(, not isEmpty([measure])) did the trick!

Upvotes: 5

Marc Polizzi
Marc Polizzi

Reputation: 9375

Not sure to understand what you mean by 'last' but here is a statement using the NonEmpty function instead of the NON EMPTY keywords:

SELECT

NON EMPTY [Measures].[Rating] ON COLUMNS,

HEAD( NonEmpty( 
         [Response].[Response Name].Members, 
         [Measures].[Rating] 
      ), 
      10
) ON ROWS

FROM [Ratings]

Upvotes: 0

Related Questions