Distinct Count

Question

Hi,

I have one fact table which contains one column and some rows have NULL values, Now I want to create a measure with aggregate function as DistinctCount but I don't want cube to calculate NULL as distinct count. So when I have created DistinctCount measure on CookieClickCount, I have received output as 4 but I want that to be 3 i.e. don't include NULL while calculating.

---

Aniruddha http://aniruddhathengadi.blogspot.com/

Answer 1

Create a set, exclude the null values and use DISTINCTCOUNT on the set

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 2

I propose a different approach ... choose if you like :)

Update the NULL values with any random values you don't obtain in data(eg: -1).

Then create a new measure with Minimum of this column(Minimum of CookieClickCount).

Now create a calculated member with below MDX::

CREATE MEMBER CURRENTCUBE.[MEASURES].[Distinct CookieClickCount]
 AS 
    iif(    
[Measures].[Min CookieClickCount]= -1,    
[Measures].[CookieClickCount] - 1,    
[Measures].[CookieClickCount]    
  ),

VISIBLE = 1; 

Here, [Measures].[CookieClickCount] is the measure what you have now.

Thanks,

Anish

---

Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)

Answer 3

If you are creating a physical DistinctCount the measure needs to be in it's own measure group anyway, so just create a view or a named query in the DSV with a WHERE clause like  "where ClickCount IS NOT NULL" and base your distinct count measure group off that. 

---

http://darren.gosbell.com - please mark correct answers

Answer 4

Hi Darren,

What if I simply create a calculated measure which will subtract NULL value  entry but do you see any query performance issue in this approach as calculated measure executes at run time.

---

Aniruddha http://aniruddhathengadi.blogspot.com/