how to calculate count distinct based on a measure in OLAP cube

Question

I'm trying to write an MDX query for a calculated member "Daily Active users" in my SSAS OLAP cube based on an attribute in the fact table called "IsDailyActive'

I have a measure called "Active Users" that uses the COUNT DISTINCT aggregation from the "Fact Activity" table on the UserId column. I want to reuse that measure and calculate distinct count of users by sepcifying the filter 'Is Daily Active' as True in my query.

I tried this, but it gives me null

WITH 
SET MySet AS
   {[Measures].[Active Users]} 
MEMBER [Measures].[Daily Active Users] AS
   DISTINCTCOUNT(MySet)
SELECT
{[Measures].[Daily Active Users]} ON COLUMNS
FROM MyDataCube


WITH 
SET MySet AS
   {[Measures].[Active Users]} 
MEMBER [Measures].[Daily Active Users] AS 
   DISTINCTCOUNT(MySet)
SELECT
{[Measures].[Daily Active Users]} ON COLUMNS
FROM MyDataCube
WHERE
([Is Daily Active].[Boolean].&[True])

What I'm doing wrong?

Note: IsDailyActive column in the "FactActivity" table is of type "bit" so it has 0 or 1 values in the rows.

the code you have included looks like two queries? Let me format the OP and you can change back if I'm wrong. — whytheq

whytheq whytheq · Accepted Answer · 2014-08-12T19:39:37

If the measure Active Users already exists in the cube script then why does the following not work?

SELECT
{[Measures].[Active Users]} ON COLUMNS
FROM MyDataCube
WHERE
([Is Daily Active].[Boolean].&[True])

how to calculate count distinct based on a measure in OLAP cube

1 Answers