# MDX Is Making Me Get Gray Hairs • ### Question

• I consider myself to be extremely proficient in SQL and thought it wouldn't be too difficult to pick up MDX...apparently I was wrong :(

Below is a query that I've been working on, with lots of help fro this community :).

```With	member measures.zero as filter([Student].[Person ID],[Measures].[At Standard] = 0).count
member measures.one as ([Student].[Person ID], [Measures].[At Standard])
Select
Non Empty measures.zero on columns,
Non Empty [Teacher].[Teacher Name].children on rows
From	[Proficiency Standards Assessment - All]
Where	(
[Proficiency Assessment Standards].[Standard Area].&[Algebra I],
[Proficiency Assessment Standards].[Diagnostic].&[False],
[Location].[Location].&[Doss High]
)

```

If I use the calculated member: measures.one in the columns axis, it returns the 4 teachers that teach Algebra I at this school, with a count of the number of students at Standard.

If I use the calculated member: measures.zero in the columns axis, it returns a list of every single teacher in the district, with a count of 1 for each teacher.

What I'm wanting is the four teachers from the "measures.one" member, and a count of the number of students not at standard, which is what I thought the filter function in the "measures.zero" member would give me.

Trying to think about this logically...I'm guessing the filter function is producing a numeric value as the output, so the association with the record fact table is getting lost?  If that's the case, what can I do about it?

Also, I should mention my "end goal" here is to create Calculated Members that I can insert into my cube (created using Visual Studio/Sql Server 2008).

Thursday, December 29, 2011 3:46 PM

• Well I managed to play around until I found the answer -- yay me!  I swear I had already tried this, but I guess I didn't have it perfect.  My final solution looks like this:

```With
member [0/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 0).count
member [1/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 1).count
member [2/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 2).count
member [3/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 3).count
Select
Non Empty
{[0/3], [1/3], [2/3], [3/3]}
on columns,
Non Empty [Teacher].[Teacher Name].children on rows
From	[Proficiency Standards Assessment - All]
Where	(
[Proficiency Assessment Standards].[Standard Area].&[Algebra I],
[Proficiency Assessment Standards].[Diagnostic].&[False],
[Location].[Location].&[Doss High]
)

```

This is giving me the number of students "at standard" for three total standards.  There is one weird thing happening with the "zero" count, but I think I'm going to open up a new thread about it.  (Basically, it seems to be counting all fields from the database where "At Standard" is 0, and not limiting/filtering it to any of the criteria in the query -- my "zero" count should be 5 and it's coming back as 71,159 ??)

My final result set looks like:

______________0/3___1/3___2/3___3/3
Ford, Paul__71159___7____9_____2

• Edited by Tuesday, January 3, 2012 6:42 PM
• Marked as answer by Tuesday, January 3, 2012 6:43 PM
Tuesday, January 3, 2012 6:41 PM

### All replies

• I continue to play with this an am getting closer, currently my query looks like this:

```With set zero as ([Student].[Student Name].children, [Measures].[At Standard])
Select
Non Empty
{
filter(zero, [Measures].[At Standard] = 1)
}	on columns,
Non Empty [Teacher].[Teacher Name].&[Ford, Paul] on rows
From	[Proficiency Standards Assessment - All]
Where	(
[Proficiency Assessment Standards].[Standard Area].&[Algebra I],
[Proficiency Assessment Standards].[Diagnostic].&[False],
[Location].[Location].&[Doss High]
)

```

This gives me a result set that looks like:

Student 1 Student 2 Student 3
AtStandard AtStandard AtStandard
Ford, Paul  1 1 1

It's basically listing out each student in their own column....what I'm wanting is a single column that gives the count of the number of students that meet that criteria.  I've tried adding a Count(function) in the "on columns" section, but then I get fussed at because it's expecting a tuple set expression.

So what I'm shooting for is a result set that would look like this:

AtStandard
Ford, Paul  3

Tuesday, January 3, 2012 5:03 PM
• Well I managed to play around until I found the answer -- yay me!  I swear I had already tried this, but I guess I didn't have it perfect.  My final solution looks like this:

```With
member [0/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 0).count
member [1/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 1).count
member [2/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 2).count
member [3/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 3).count
Select
Non Empty
{[0/3], [1/3], [2/3], [3/3]}
on columns,
Non Empty [Teacher].[Teacher Name].children on rows
From	[Proficiency Standards Assessment - All]
Where	(
[Proficiency Assessment Standards].[Standard Area].&[Algebra I],
[Proficiency Assessment Standards].[Diagnostic].&[False],
[Location].[Location].&[Doss High]
)

```

This is giving me the number of students "at standard" for three total standards.  There is one weird thing happening with the "zero" count, but I think I'm going to open up a new thread about it.  (Basically, it seems to be counting all fields from the database where "At Standard" is 0, and not limiting/filtering it to any of the criteria in the query -- my "zero" count should be 5 and it's coming back as 71,159 ??)

My final result set looks like:

______________0/3___1/3___2/3___3/3
Ford, Paul__71159___7____9_____2

• Edited by Tuesday, January 3, 2012 6:42 PM
• Marked as answer by Tuesday, January 3, 2012 6:43 PM
Tuesday, January 3, 2012 6:41 PM