Count Distinct - it's counting blanks

ekola

Hi - I'm using COUNT (DISTINCT to get a unique count of ID numbers and there are some records where the ID number is intentionally blank. I want these records to be excluded from the count entirely but this function seems to be counting 'blank' as its own unique value and is therefore adding 1 to the unique count which is throwing everything off. How can I exclude blanks from this count?

MarkSnodgrass

I'm assuming you are doing this at the card level. You can do this a couple different ways.

1. If you don't need the blank records, drag the ID field into the filter section and tell it to exclude blank records. Then, your count distinct should work as is.
2. If you need the blanks but don't want to count them you can write a beast mode like this:

SUM(CASE WHEN TRIM(ID) = '' OR ID IS NULL THEN 0 ELSE 1 END)

Hope this helps.