Tuesday, April 03, 2012 12:53 PM
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.
Tuesday, April 03, 2012 3:28 PMCreate 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
Tuesday, April 03, 2012 8:47 PM
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]VISIBLE = 1;
[Measures].[Min CookieClickCount]= -1,
[Measures].[CookieClickCount] - 1,
Here, [Measures].[CookieClickCount] is the measure what you have now.
Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)
Wednesday, April 04, 2012 3:24 AMModeratorIf 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
- Proposed As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Thursday, April 05, 2012 8:24 AM
Thursday, April 05, 2012 11:39 AM
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.