CODEWITHSUNDEEP
sql-servermdxbusiness-intelligenceperformancepoint
Hammad
Hammad

Reputation: 171

MDX Group & Count

I'm having the below MDX Query 

WITH 
  MEMBER Measures.Improvement AS
     [Measures].[School Evaluation] 
   - 
     (
      [Measures].[School Evaluation]
     ,[Cycle].[Name].CurrentMember.PREVMEMBER
     )
  MEMBER Measures.PreviousEvaluation AS
     (
      [Measures].[School Evaluation]
     ,[Cycle].[Name].CurrentMember.PREVMEMBER
     )
  SELECT
     Measures.Improvement ON COLUMNS,
     Filter (
       { [Cycle].[Name].[Name].ALLMEMBERS }
     * { [School].[Name En].[Name En].ALLMEMBERS }
     , Measures.PreviousEvaluation > 0 
        AND 
         [Measures].[School Evaluation] > 0
     )
     ON ROWS
  FROM [SchoolCube];

This code generates the below output

enter image description here

Now what I need is to count the occurrence of Improvement "-2,-1,0,..." across all the schools So I have something like this

enter image description here

How Can I achieve this?

Thanks,

Upvotes: 2

Views: 1057

Answers (1)

Michael Schröder
Michael Schröder

Reputation: 166

You have to add another dimension "Improvement" that holds possible values for either a fixed range, e.g. -10..+10 or you build the range dynamically based on your data.

Add a second measure group to the cube based on that dimension table and create a measure "Improvement base", that sums the improvement value. This is a helper measure to simplify the following steps.

Now you can create a new calculated measure:

CREATE MEMBER CURRENTCUBE.[Measures].[Count Improvements] AS
SUM(IIF([Measures].[Improvement] = [Measures].[Improvement base], 1, 0));

Maybe you have to scope the All-member of the Improvement dimension to sum the children.

Upvotes: 1

Related Questions