How does SSAS interpret NULL & Zeroes regarding counts?

Posted on 2011-10-26
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
    LVL 73

    Accepted Solution

    COUNT,  like all aggregate functions, ignores nulls

    select count(your_column) from your_table

    will ignore the null values in your_column

    Author Comment

    Does aggregate functions COUNT on zeroes as well?

    Author Comment

    Ignore question above this one.

    Do aggregate functions count on zeroes?
    LVL 73

    Assisted Solution


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

    Expert Comment

    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

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now