hi,

i have a cube which is based on a many to many relation

patients--one record for each patient

diagnoses--one patient can have many diagnoses, vice versa

procedures--one patient can have many procedures, or vice versa.

i have the cube which linkes the diagnoses and procedures to patients using a many to many relation.

if i want to understand the categoreis thar are defined by specifc diagnosis and procedure codes, how to accomplish

categoryA --diagnosis AA and procedure BB

categoryB--diagnosis AA and procedure BC

categoryC--diagnosis BB and procedure BB

The Report at the end shud look like

category A--10 patients

category B-11 patients

category C --12 patients

for each category, i need to have a separte pivot table and pick the selected diagnosis and procedure codes , but i want to do everything in 1 pivot table, pls help

Hi Neophtenik,

According to your description, you want to count the numbers of the patient on each category, right? In this case, you can use Filter function to return the set that results from filtering a specified set based on a search condition. And then use the Count function to return the numbers of patient.

Besides, here is a blog about Optimizing Count(Filter(...)) expressions in MDX, please see:
Optimizing Count(Filter(...)) expressions in MDX

Hope this helps.

Regards,