How does SSAS interpret NULL & Zeroes regarding counts?

Posted on 2011-10-26
Medium Priority
Last Modified: 2016-02-14
I have a measure where the data in the column is based on currency.  The results of the stored procedure which creates this column and inserts data looks like this:

<blank>  Stored Procedure uses a CASE statement and the ELSE is NULL (is <blank> correct?)

I want to perform a count on everything that IS NOT NULL.
How can I do this in my cube?
Question by:rhservan
  • 3
  • 2
LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 37031173
COUNT,  like all aggregate functions, ignores nulls

select count(your_column) from your_table

will ignore the null values in your_column

Author Comment

ID: 37031977
Does aggregate functions COUNT on zeroes as well?

Author Comment

ID: 37032658
Ignore question above this one.

Do aggregate functions count on zeroes?
LVL 74

Assisted Solution

sdstuber earned 2000 total points
ID: 37032678

count (and all aggregates)  operate on all non-null values.  including 0 (zero)
LVL 74

Expert Comment

ID: 37032689
if you want your count to ignore zero values as well as null's then try something like this...

select count(case when your_column = 0 then null else your_column end) from your_table

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question