KB30684: How to get the correct count for attribute elements which have a many to many relationship in MicroStrategy Developer 9.x and above.

Some users may have attribute elements that contain attributes with elements that have a many to many relationship, however they want to do calculate a distinct count. The following steps below will outline an example in Strategy Tutorial of how to achieve this.
 
 

• In the Strategy Tutorial Warehouse, create a new table as follows:
   
  
• Add the table to the Project and create attributes Patient and Ops with a many to many relationship.
• Create a metric CountPatient and set the count parameters to Distinct = True.
• Create a report with the attributes Patient, and Ops and metric CountPatient in it.
• On the grid from Data Subtotals set the subtotal to Count.
• When the report is executed the Subtotal is shown incorrectly as 5 for patient count as shown below:
   
  

• The Group by in the report SQL is being done on Patient and Ops which is a child of Patient.
• In order to get the correct count of number of patients in the report, remove Report Level dimensionality from the metric and in its place add Patient with Filtering/Grouping set to standard.
• When the report is executed, the Group by in the report SQL is being done on Patient and the Patient count shows shows up correctly as 3, as displayed in the screenshot below: