October 24, 2011 at 12:51 pm
This query on my Fact Table works just the way I want:
SELECT COUNT (DISTINCT ORDERNUMBERUNIQUE) AS COUNT
FROM FACTTABLE1
WHERE NOCHARGEAMOUNT > 0 AND NOCHARGEAMOUNT IS NOT NULL
My plan is to create a Calculated Member:
The measure [Measures].[ORDERNUMBERUNIQUE] <-------Measure configured as distinct count
How can I complete this calculated member where it is filtered as the SQL WHERE statement above, so that I have a single column ORDERNUMBERUNIQUE Count
October 24, 2011 at 8:31 pm
LearningSSAS (10/24/2011)
This query on my Fact Table works just the way I want:SELECT COUNT (DISTINCT ORDERNUMBERUNIQUE) AS COUNT
FROM FACTTABLE1
WHERE NOCHARGEAMOUNT > 0 AND NOCHARGEAMOUNT IS NOT NULL
My plan is to create a Calculated Member:
The measure [Measures].[ORDERNUMBERUNIQUE] <-------Measure configured as distinct count
How can I complete this calculated member where it is filtered as the SQL WHERE statement above, so that I have a single column ORDERNUMBERUNIQUE Count
Add a column in the dsv for FACTTABLE1:
SELECT col1, col2, col3,
, CASE WHEN NOCHARGEAMOUNT > 0 --test against null is not needed, as null is not > 0
THEN ORDERNUMBERUNIQUE
ELSE NULL
END ORDERNUMBERUNIQUE_FILTERED
FROM FACTTABLE1
Then add a standard count distinct measure for ORDERNUMBERUNIQUE_FILTERED
October 25, 2011 at 7:36 am
Logically speaking you are correct. However, the bigger issue is I intend to perform a COUNT on this column and I understand that whether it is NULL or 0 it is still counted when using COUNT.
When I perform the column count with zeroes I get 350,000 without zeroes I get 15,000
Wouldn't this also be true if I used NULL?
October 25, 2011 at 2:20 pm
LearningSSAS (10/25/2011)
Logically speaking you are correct. However, the bigger issue is I intend to perform a COUNT on this column and I understand that whether it is NULL or 0 it is still counted when using COUNT.When I perform the column count with zeroes I get 350,000 without zeroes I get 15,000
Wouldn't this also be true if I used NULL?
Null's aren't counted:
CREATE TABLE #t (a INT IDENTITY NOT NULL PRIMARY KEY, b INT)
INSERT INTO #t (b) VALUES (0)
INSERT INTO #t (b) VALUES (0)
INSERT INTO #t (b) VALUES (0)
INSERT INTO #t (b) VALUES (0)
INSERT INTO #t (b) VALUES (0)
INSERT INTO #t (b) VALUES (1)
INSERT INTO #t (b) VALUES (1)
INSERT INTO #t (b) VALUES (2)
INSERT INTO #t (b) VALUES (3)
INSERT INTO #t (b) VALUES (null)
INSERT INTO #t (b) VALUES (null)
INSERT INTO #t (b) VALUES (null)
INSERT INTO #t (b) VALUES (null)
INSERT INTO #t (b) VALUES (null)
INSERT INTO #t (b) VALUES (null)
INSERT INTO #t (b) VALUES (null)
INSERT INTO #t (b) VALUES (null)
-- returns 17
select count(*) from #t
-- doesn't include nulls, returns 4 (0,1,2,3)
SELECT COUNT(DISTINCT b) FROM #t
-- returns 3 (1,2,3)
SELECT COUNT(DISTINCT b) FROM #t WHERE b > 0 AND b IS NOT NULL
-- also returns 3
SELECT COUNT(distinct b) FROM #t WHERE b > 0
-- doesn't include nulls, returns 9 (0,0,0,0,0,1,1,2,3)
SELECT COUNT( b) FROM #t
-- returns 4 (1,1,2,3)
SELECT COUNT( b) FROM #t WHERE b > 0 AND b IS NOT NULL
-- also returns 4 (1,1,2,3)
SELECT COUNT( b) FROM #t WHERE b > 0
DROP TABLE #t
October 25, 2011 at 2:20 pm
(duplicate post)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply