October 28, 2009 at 9:44 am
My measure group contains only a single measure that applies a distinct count aggregation, that also counts null as 1. Is there a way to not count the null records.
eg. Country Desc
USA
England
null
The distinct count measure group result is 3
The desired result is 2
October 28, 2009 at 9:55 am
Hi,
You could just add a WHERE condition to say:
WHERE country != 'null'
I've noticed that you are storing the null value as text, i.e. 'null'.
If you stored the null as an actual NULL data entry then you wouldn't have this problem.
I would suggest that you update all your 'null' text values to a NULL data entry. This would save on space and remove your problem.
UPDATE CountryTable
SET CountryColumn = NULL
WHERE CountryColumn = 'null'
Below is an example of what I mean between 'null' and NULL.
There are 4 values inserted, one of each type of null value. You'll notice that with my suggestion above you would get the correct value.
create table #t (t varchar(10))
insert into #t
select 'usa'
insert into #t
select 'uk'
insert into #t
select 'null'
insert into #t
select NULL
select COUNT(t)
from #t
drop table #t
Hope that helps,
Lewis
November 3, 2009 at 6:12 am
or on the fact table add a column of value 1. This means that you can always retrieve the number of records. Very usefull thing to do generally.
Mark.
😎
November 29, 2011 at 12:17 pm
I had the same problem.
I solved it by creating a new view from exisitng fact table with a WHERE condition ISNULL(Country_Code,0) <> 0 and then creating a Distinct Count of Country_Code out of this view
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply