# Rookie Question - Trying to Report on the sum of Distinct Measure Values

• ### Question

• I have a measure table that stores, among other things, the number of questions that students answer correctly for a test.  I need to create a report that lists the number of correct answers for each test: 0 Correct - 3 students;  1 Correct - 5 students; 2 Correct - 7 students; etc. etc.

I just have a single column storing this information in the measure table called NumCorrect.  So when setting up my cube -- is there a way to create a measure that will list out this information?  I'm thinking I may have to create a calculation, but so far I'm not having much luck...

Wednesday, December 14, 2011 5:56 PM

• "it's not limiting it to the number of correct answers for just this teacher"

That is right, you get all the students which have score specified. Next step is to slice that data which is where your WHERE clause comes in. Like:

```Select
{ filter([Student].[Person ID].members, [Measures].[Diagnostic Score] = 3) } on columns
From [Proficiency Standards Assessment - Diagnostic]
WHERE ([dimLocation].[LocationName].&[Doss High], [Teacher].[Teacher Name].&[S, Kent] )
```

Here you are saying give me all students who have Score=3 for [S, Kent] teacher at [Doss High] location. You may add Test dimension as well to refine your results.

Other ways to view can be by using NONEMPTYCROSSJOIN or CROSSJOIN to check the relation between teacher and student.

Hope it helps.

http://dailyitsolutions.blogspot.com/
• Marked as answer by Monday, December 19, 2011 1:18 PM
Friday, December 16, 2011 6:09 PM

### All replies

• 1. You need to mention which tool you would be using to build report; PerformancePoint, SSRS, Excel etc.?

2. You would need to provide more information on the data model like fact tables and dimensions you have.

If you have a students dimension, you might have to create multiple (one for each NumCorrect?) calculations with FILTER and COUNT to give you the results you are looking for like:

```FILTER( [Dim Student], Measures.NumCorrect =0).Count
```

http://dailyitsolutions.blogspot.com/
Wednesday, December 14, 2011 8:23 PM
• Thanks!  This is being developed in SQL Server 2008, and I will be using either PerformancePoint 2010 or Excel 2007 for the reporting tool -- most likely Excel 2007.

My cube will the following dimensions (only listing pertinent members):

DimStudent
--StudentID
--StudentName

DimLocation
--LocationID
--LocationName

DimTeacher
--TeacherID
--TeacherName

DimTest (Don't know if it matters/would be needed, but Test Dim does NOT contain # of Questions per test)
--TestID
--TestName

One Fact Table:

testResults
--StudentID
--LocationID
--TeacherID
--TestID
--NumCorrect
--Score
--PassFail (will contain a 1 or 0 for Pass or Fail)

I have been playing around with the Filter function, but I've not been including any of the dimension members in it, just the measure like:

filter([Measures].[Diagnostic Score],3) or filter([Measures].[Diagnostic Score],3)

The results is just a blank cell or an error message about improper syntax.  One question I have -- I've been setting the measure aggregation to "No Aggregation" -- is this correct?

• Edited by Thursday, December 15, 2011 12:46 PM
Wednesday, December 14, 2011 8:52 PM
• I feel like this is turning into my online journal of self-discovery :).  I keep playing and trying to figure this out.  I currently have the NumCorrect measure set to Sum.  If I run a basic MDX query for a specific Teacher, it gives me the sum of NumCorrect for all Students, for that teacher, which is what I would expect:

Select
{  [Measures].[NumCorrect] } on columns,
[dimTeacher].[TeacherName].&[S, Kent] on rows
From [Proficiency Standards Assessment - Diagnostic]

However, I'm still struggling with trying to get counts on the specific measure values.  The closest I've come, when using the filter, is to have every student in the fact table listed with a "1" if the value is true, even if I add additional filtering:
Select
{ filter([Student].[Person ID].members, [Measures].[Diagnostic Score] = 3) } on columns,
[Teacher].[Teacher Name].&[S, Kent] on rows
From [Proficiency Standards Assessment - Diagnostic]
WHERE ([dimLocation].[LocationName].&[Doss High]
Am I getting closer?
• Edited by Thursday, December 15, 2011 3:45 PM
Thursday, December 15, 2011 3:44 PM
• You're pretty close. Have you tried using the following to get count on the specific measure values:

`FILTER ( [Student].[Person ID].members,  [Measures].[Diagnostic Score] = 3 ).COUNT`
This should give you the count on a specific measure value. Try switching [Diagnostic Score] with [NumCorrect].

http://dailyitsolutions.blogspot.com/
Friday, December 16, 2011 3:15 PM
• Thanks for all your help, I'm getting closer but it's still not quite there...I hate being in the learning stages with new languages :(

I haven't added the Count function yet, because I wanted to see the Filter function in action.  What is happening is the query will return a list of all students for this teacher, with the number of correct answers (as supplied by the Filter function) for ALL tests given by even other teachers, it's not limiting it to the number of correct answers for just this teacher.

So if I have the filter say, "show me all students for this teacher who have 2 correct answers", it searches the fact table for students who report to this teacher, and have 2 correct answers, regardless of which teacher they correctly answered them for.

After thinking about it, it made sense - my query is just stating to get total correct answers for each student, and then displaying those answers for the specified teacher.

Here's where my still-limited knowledge of how MDX works is killing me.  I thought I could just change my query in the columns section from:

Select
{ filter([Student].[Person ID].members, [Measures].[NumCorrect] = 3) } on columns

to

Select
{ filter([Teacher].[Person ID].members, [Measures].[NumCorrect] = 3) } on columns

and that would return the number of correct answers for the specific teachers.  However, this returns nothing, I just have a row with the teachers name and no column data returned.

Next I thought, maybe I need to tie in all three -- Teacher ID, Student ID, and Num Correct; however, this doesn't seem possible -- I get fussed at by the compiler for trying to provide three arguments in a set or tuple.

Have a done I good job of thoroughly confusing you yet? :)

Friday, December 16, 2011 4:34 PM
• "it's not limiting it to the number of correct answers for just this teacher"

That is right, you get all the students which have score specified. Next step is to slice that data which is where your WHERE clause comes in. Like:

```Select
{ filter([Student].[Person ID].members, [Measures].[Diagnostic Score] = 3) } on columns
From [Proficiency Standards Assessment - Diagnostic]
WHERE ([dimLocation].[LocationName].&[Doss High], [Teacher].[Teacher Name].&[S, Kent] )
```

Here you are saying give me all students who have Score=3 for [S, Kent] teacher at [Doss High] location. You may add Test dimension as well to refine your results.

Other ways to view can be by using NONEMPTYCROSSJOIN or CROSSJOIN to check the relation between teacher and student.

Hope it helps.

http://dailyitsolutions.blogspot.com/
• Marked as answer by Monday, December 19, 2011 1:18 PM
Friday, December 16, 2011 6:09 PM
• Thank You very much, I guess I was thinking that by placing the Teacher in the "rows" section that would act as a slicer, but what you explained makes sense.  My teachers are gonna be so happy!!

Hope everybody has a Great Holiday Season!!

Monday, December 19, 2011 1:19 PM