Answered by:
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
Answers

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
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 1It'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 3Tuesday, 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
Tuesday, January 3, 2012 6:41 PM