Truly Distinct Count in Hierarchy (MDX)

Question

I am working with data in an OLAP-cube setup. I am wanting to display all Math Student Groups with the distinct student count for each group. Students get placed into a Math group based on how they scored on their math placement exam. They also get placed in every Math group lower than the one they placed into. (placed into Math 106, also a member of Math 105, 104, 103, 102, 101 and 100) Currently I am able to show only Math Groups on my rows and Distinct Students as a measure on the columns. The counts that display are only distinct per row and not for the entire report. So Math 100 group is displaying the count of all students in Math groups, not just Math 100.

WITH 
/*- Qv6.0.3431.1887 -*/

SELECT  
NON EMPTY 
{
 DISTINCT ( { [Measures].[Distinct Students] } ) 
} 
PROPERTIES PARENT_UNIQUE_NAME,  MEMBER_KEY, MEMBER_TYPE 

 ON COLUMNS, 
NON EMPTY 
{
  { [Student].[Student].[All Students] } 
  *  { {  {  DISTINCT(
 {  {  FILTER(
 {  {  FILTER(
 { DESCENDANTS( [Student Group].[Student Group].[All Student Groups] ,     [Student Group].[Student Group].[Student Group])  }  ,  InStr(1,IIF( ISERROR(   [Student Group].[Student Group].CURRENTMEMBER.MEMBER_CAPTION ),"", [Student Group].[Student Group].CURRENTMEMBER.MEMBER_CAPTION ) , "Math") <> 0
)  } 
 }  ,  InStr(1,IIF( ISERROR( [Student Group].[Student     Group].CURRENTMEMBER.MEMBER_CAPTION ),"", [Student Group].[Student        Group].CURRENTMEMBER.MEMBER_CAPTION ) , "eligible") <> 0
)  } 
 } )  } 
 }   }  
} 
PROPERTIES PARENT_UNIQUE_NAME,  MEMBER_KEY, MEMBER_TYPE 

 ON ROWS 

FROM [Student Term]  
WHERE ( [Terms].[Terms].[Term].&[138] ) 
CELL PROPERTIES FORMATTED_VALUE, VALUE, FONT_NAME

Here is a screenshot of the output: Math Groups Student counts

I have tried searching all over here but was not successful in finding anything to help. I am by no means proficient in MDX, the reporting tool we use creates it based on what items we choose in the report builder (drag and drop mostly). The tool does allow you to create custom measures and elements using MDX. I really appreciate all assistance and please let me know if more information is needed.

Thanks!

====UPDATE 1-FEB-2017======= thanks for your reply. I am finally getting to work on this again. I created custom measures in our front-end tool based on your code. I changed the [Measures].[StudentsCount] to [Measures].[Distinct Students] to match an available measure. If I select the 'StudentsCountDC' custom measure and run the report it returns nothing. If i select the 'IsStudentsTop' custom measure the counts it returns are extremely large like it isn't running for the selected term. Here is the new MDX that was generated after I ran the report with the newly created custom measure, 'StudentsCountDC'.

WITH 
/*- Qv6.0.3431.1887 -*/

SELECT  
NON EMPTY 
{
 DISTINCT ( { [Measures].[#~~2d0c2a98-fed9-4797-a573-170d3e450e4c~~#] } ) 
} 
PROPERTIES PARENT_UNIQUE_NAME,  MEMBER_KEY, MEMBER_TYPE 

 ON COLUMNS, 
NON EMPTY 
{
  { [Student].[Student].[All Students] } 
  *  { [#~~9faf06d9-a022-493d-9dfd-dadd55793c37~~#] }  
} 
PROPERTIES PARENT_UNIQUE_NAME,  MEMBER_KEY, MEMBER_TYPE 

 ON ROWS 

FROM [Student Term]  
WHERE ( [Terms].[Terms].[Term].&[136] ) 
CELL PROPERTIES FORMATTED_VALUE, VALUE, FONT_NAME

screenshots of new measures: IsStudentsTop & StudentsCountDC measures Thanks again for your help!

Danylo Korostil Danylo Korostil · Accepted Answer · 2017-01-26T13:11:07

It's a bit tricky, I personally haven't tested it yet. Hope, you'll get the point:

with 

Member [Measures].[StudentsCountDC] as
SUM(
    [Student].[Student].[Student].Members,
    IIF(
        not [Measures].[IsStudentsTop]
        and 
        [Measures].[StudentsCount],
        1,
        NULL
    )
)

Member [Measures].[IsStudentsTop] as
SUM(
    {[Student Group].[Student Group].PrevMember:NULL},
    [Measures].[StudentsCount]
)

IsStudentsTop checks whether StudentsCount exists in the upper groups (ordered by your attribute). StudentsCountDC checks if the current student has empty IsStudentsTop and StudentsCount is non empty, then returns 1 for each student, else returns Null.

Truly Distinct Count in Hierarchy (MDX)

1 Answers