CODEWITHSUNDEEP
mdxolap
Felipe
Felipe

Reputation: 7401

Filtering dimensions in MDX inside a SUM

I am new to MDX expressions and I am trying to create one that sums the value of a given measure filtered by dimensions.

In my database I have several different dimensions that have the same name: "Answer". To sum them up, I have created the query below:

WITH MEMBER Measures.Total as SUM ({[Activity].[Activity].&[14], [Activity][Activity].&[22]}, 
[Measures].[Activity time])
SELECT NON EMPTY [Measures].[Total] on COLUMNS from  [My Analytics]

This query works, however I had to use the "&[14]" and "&[22]" statments that correspond to two different "Answer" dimensions.

Since I have more than two dimensions with the same name, is there a way to rewrite the query above in a way that I would select all these dimensions without having to add their unique ID? For example, I would re-write the query as something like this:

WITH MEMBER Measures.Total as SUM ({[Activity].[Activity].&["Answer"]}, 
[Measures].[Activity time])
SELECT NON EMPTY [Measures].[Total] on COLUMNS from  [My Analytics]

Is this possible?

Thanks!

Upvotes: 2

Views: 7176

Answers (1)

Marc Polizzi
Marc Polizzi

Reputation: 9375

You can use the Filter function as following:

with 
  set [my-answers] as 
      Filter( [Activity].[Activity].members, 
              [Activity].[Activity].currentMember.name = 'Answer' 
      )

   member [Measures].[Total] as Sum( [my-answers] )

...

Upvotes: 2

Related Questions