How to add where clause/filter into calculated member in mdx

• sqlblue

  SSCrazy

  Points: 2324

  More actions

  March 15, 2014 at 7:30 pm

  #150082

  I am new to MDX. I have below query:

  WITH MEMBER [Student Study Length Hours]

  AS ([Measures].[Study Length Minutes]/[Measures].[Students]/60.0)

  SELECT {[Student Study Length Hours]

  ,[Measures].[Study Length Minutes]

  ,[Measures].[Students} ON COLUMNS

  FROM [Classroom]

  WHERE {[Student Participants].[Student Type].&[100], [Student Participants].[Student Type].&[400]}

  In the cube, I then created the below calculated member, using the info from the query above, and I wanted to included the

  conditions I have in the where clause above, but I do not know how to add those conditions into the calculated member below,

  as I understand the WHERE clause is not allowed in the calculated member. Any help and input you can provide would be greatly appreciated.

  CREATE MEMBER CURRENTCUBE.[Measures].[Student Study Length Hours]

  AS ([Measures].[Study Length Minutes]/[Measures].[Students]/60.0),

  VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Student Study';

  I have googled all over, but I have not been able to find one example addresses the where clause I have as above.

  In SQL, it would be the where clause with StudentType IN(100,400). But how do I do that in calculated member?

• sneumersky

  SSCertifiable

  Points: 7667

  More actions

  March 20, 2014 at 12:04 pm

  #1698826

  First try moving the set in the WHERE clause to the "ON ROWS" axis. Do you then get what you want?