MDX Group & Count

Question

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

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

How Can I achieve this?

Thanks,

@sagi can you expand your comment - this is an interesting question - I don't think the answer to it is trivial: if the answer is as easy as a Google search can you please supply the exact url. — whytheq, May 09 '16 at 21:34

score 1 · Answer 1 · answered May 11 '16 at 13:42

1

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.

answered May 11 '16 at 13:42

Michael Schröder

166
1
1
8

First, Thank you for supporting me in this topic. I started to execute these steps. But after I create the "Improvement base" measure. the solution failed to build as "MeasureGroup [SchoolCube].[Improvement] : No dimension relationships exist. The measure group 'Improvement' is not related to any dimensions". I couldn't find away to relate the Improvement base with my dimensions. Thanks, – Hammad May 26 '16 at 09:31
If you open the cube designer you can find the dimension usage on the second tab of the designer. You have to link your measure group "Improvement" (is listed in the columns) to your dimension "Improvement" (which you'll find on the rows). On the intersection of your dimension and the measure group there is either a text box with a description of your link or there is a grey box. In the latter click on the ellipsis button at the right of the grey box to create a new link between the dimension and the measure group. – Michael Schröder May 30 '16 at 06:28
If both your dimension and measure group use **exact** the same table or view (or even query), than you can select the fact relationship, otherwise select a regular relationship and select the proper attribute (normally the key attribute of your dimension) and the correct fact column. – Michael Schröder May 30 '16 at 06:30

MDX Group & Count

1 Answers1