DISTINCT Count and NULL Processiong

In short, I have a measure that counts the distinct number of customers.

I recently learned that it's counting a NULL value as 1 and I don't want this. Are there some properties that can stop the NULL value from being counted? I tried playing with a few of the properties that I found, but no luck.

The only solution that I have thought of so far is creating another table in the DSV that explicitly filters out the NULL values in the WHERE clause and then using it as the source for the DISTINCT COUNT. However, I'd like to avoid this solution if possible.

Maybe some MDX in a Calculated Member is needed? If so, please provide the pseudo-MDX as my MDX is definitely weak.

Any thoughts?

By the way, it's a AS2005 cube using SQL 2005 Standard Edition.

How about creating a new, hidden dimension (or attribute on your customer dimension) with two members: one member represents rows in the fact table where your Customer is null, and one for where it's not null. What you could then do is keep your distinct count measure as it is, but then overwrite it in the MDX Script so it only returns the value for the not-null member - something like this:

(Measures.[Customer Count])=(Measures.[Customer Count], [Hidden Dimension].[Hidden Attribute].[Not Null Customers]);

HTH,

Chris

|||Or, even better, make [Not Null Customers] member to be a default member in that dimension, and then you won't need to do anything in MDX Script at all, and it will be better for performance. My preference, however, is to do the DSV change that you want to avoid, I think that is the best solution.