Mdx calculate count distinct

Question

I want to write a mdx script that displays the count rows I have for a member.

This is my initial script:

  SELECT NON EMPTY { [Measures].[I_OPC_ATTEINT]
 and 6 measures } ON COLUMNS
, NON EMPTY { ([Axe_Temps].[MOIS_ANNEE].[MOIS_ANNEE].ALLMEMBERS 
* [Axe_ORGANISATION].[Structure].[EQUIPE].ALLMEMBERS 
* [Axe_OPC].[TYPE_REGROUPEMENT].[TYPE_REGROUPEMENT].ALLMEMBERS 
* [Axe_OPC].[COMPOSITION].[COMPOSITION].ALLMEMBERS 
* [Axe_OPC].[OPC].[OPC].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION,    MEMBER_UNIQUE_NAME ON ROWS 
FROM ( SELECT ( STRTOMEMBER('[Axe_ORGANISATION].[CODE_EQUIPE].&[E_1001]') ) ON COLUMNS 
FROM ( SELECT ( STRTOMEMBER('[Axe_ORGANISATION].[CODE_PLATEAU].&[D_1000]') ) ON COLUMNS 
FROM ( SELECT ( STRTOMEMBER('[Axe_ORGANISATION].[CODE_UNITE].&[U_107864]') ) ON COLUMNS 
FROM ( SELECT ( STRTOMEMBER('[Axe_ORGANISATION].[CODE_CANAL].&[AVSC]') ) ON COLUMNS 
FROM ( SELECT ( STRTOMEMBER('[Axe_Temps].[MOIS_ANNEE].&[201306]') ) ON COLUMNS 
FROM [PVC_Reporting])))))

I want to display 2 calculated measures:

The count of rows of my result ( count distinct ( [Axe_OPC].[COMPOSITION].[COMPOSITION].ALLMEMBERS )
The count of rows where [Measures].[I_OPC_ATTEINT] <> 0

Thank you.

Marc Polizzi Marc Polizzi · Accepted Answer · 2013-07-27T01:05:40

I would create a measure of type 'distinct count' within the cube and create a simple dimension (oui|non) for I_OPC_ATTEINT.

Mdx calculate count distinct

1 Answers