CODEWITHSUNDEEP
countmdxdimensions
ewan
ewan

Reputation: 73

MDX COUNT ON DIMENSION

I have a mdx query which can give me distinct eid, region and country:

SELECT ([Measures].[Active Tiles Count]) ON 0,
NON EMPTY ([Employee].[Employee Id].Children,[Employee - Business Region].  [Region Code].Children,[Employee - Country].[Country Code].Children) ON 1
FROM [OLAP Pre]
WHERE ( 
{[Employee Statuses].[Status Id].&[1],[Employee Statuses].[Status Id].&[4],[Employee Statuses].[Status Id].&[3]},
{[Business Region].[Abbreviation].&[APJ],[Business Region].[Abbreviation].&[EMEA],[Business Region].[Abbreviation].&[Americas]},
{[Employee Types].[Bits].&[1],[Employee Types].[Bits].&[9],
[Employee Types].[Bits].&[25],[Employee Types].[Bits].&[5],
[Employee Types].[Bits].&[13],[Employee Types].[Bits].&[29]}
,{[Date].[Date].&[2015-03-18T00:00:00]:NULL})

But I want COUNT(DISTINCT(employEEid)) grouped by region and country.
How to do this?

Upvotes: 0

Views: 181

Answers (2)

ewan
ewan

Reputation: 73

Create a DISTINCT COUNT MEASURE, It's a Predefined aggregation types

Upvotes: 1

SouravA
SouravA

Reputation: 5243

Try making use of the DISTINCTCOUNT function

WITH MEMBER Measures.[CountOfDistinctEmployes] AS
DISTINCTCOUNT([Employee].[Employee Id].Children)

SELECT {[Measures].[Active Tiles Count], Measures.[CountOfDistinctEmployes]} ON 0,
NON EMPTY ([Employee].[Employee Id].Children,[Employee - Business Region].  [Region Code].Children,[Employee - Country].[Country Code].Children) ON 1
FROM [OLAP Pre]
WHERE ( 
{[Employee Statuses].[Status Id].&[1],[Employee Statuses].[Status Id].&[4],[Employee Statuses].[Status Id].&[3]},
{[Business Region].[Abbreviation].&[APJ],[Business Region].[Abbreviation].&[EMEA],[Business Region].[Abbreviation].&[Americas]},
{[Employee Types].[Bits].&[1],[Employee Types].[Bits].&[9],
[Employee Types].[Bits].&[25],[Employee Types].[Bits].&[5],
[Employee Types].[Bits].&[13],[Employee Types].[Bits].&[29]}
,{[Date].[Date].&[2015-03-18T00:00:00]:NULL})

Upvotes: 0

Related Questions