Hi

I am doing a distinct count of a measure and at each level it is 1 more
than the count. Seems like it is counting NULL value also. Can someone
help me to solve this?

Thank you



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Let's suppose next some fact records.

EmpID ProdID Sales Cost Const(Dummy)
----------------------------------------
1 1 100 100 1
1 2 NULL 100 1
2 1 NULL 100 1
2 2 NULL 100 1
2 3 300 100 1

Case of EmpID 1, SumOfConst is 2 and CountOfSales is 1.
Case of EmpID 2, SumOfConst is 3 and CountOfSales is 1.
Case of ProdID 1, SumOfConst is 2 and CountOfSales is 1.
Case of ProdID 2, SumOfConst is 2 and CountOfSales is 0.
Case of ProdID 3, SumOfConst is 1 and CountOfSales is 1. -> No null
Case of All, SumOfConst is 5 and CountOfSales is 2.

So, if both of the results are not same, it means there is more than one NULL.

Your case might have more things considered but I hope next logical
expression could be helpful.

IIF(SumOfConst = CountOfSales, DistinctCount, DistinctCount - 1)

Ohjoo Kwon
www.olapforum.com


"ram lakshman" wrote:

Analysis Services 2000 does not support NULLS as you would expect it in a
relational RDBMS with 3-level logic. It simply converts all NULLs to zeros.
Thus they will be counted (as you are seeing).

BTW: this is not true with 2005 -- there we support NULLs and unknown
members.

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Ohjoo Kwon" <Ohjoo Kwon (AT) discussions (DOT) microsoft.com> wrote

As you said, when Analysis Services compares or calculates a NULL expression
with a non-NULL expression (including constant), the NULL is converted to
zero. This is true in every formula or SUM aggregation.

But a Count measure calculates its value by tallying the number of non-empty
occurrences of the source field in the fact table, which means NULL is not
counted.

However a Distinct Count measure calculates the number of unique occurrences
of a source field in the fact table and it considers NULL as a distinct
occurrence.

Am I wrong?



"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote

There are two concepts which are being mixed here:

1. What happens with NULLs inside the fact table during processing. This is
what Dave refered to - in AS2K they get converted to zeros. In AS2005 it is
possible to keep them NULLs.
2. However, neither AS2K nor AS2005 will have 3-value-logic behavior of
NULLs like SQL does, simply because MDX has different rules - NULLs work in
AS2005 inside MDX exactly same way as in AS2K.

--
==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebLog/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================