- There are three functions in DAX
to compute the number of values in a column
that are not blank.
They are COUNT, COUNTA and COUNTX.
There are no differences at all
between COUNT and COUNTA,
that's why we only cover COUNT and COUNTX.
Beside, COUNT is nothing but
the short version of COUNTX.
You can use COUNT whenever you need to count the number
of rows with a value and the expression you use
is just one column from the model.
Let's see some code.
The first calculation, number of customers
counts the number of customers.
In Asia, we have 3,658 customers.
By using COUNT over customer name,
we are counting the number of customers
that actually have a name
where the name is not blank.
And of course, the number is slightly smaller.
You can obtain the very same expression
if you use COUNTX over customer of customer name
because in this case, the formula we are using
contains only one column.
Therefore, COUNT and COUNTX,
they provide the same result.
You can also obtain the same calculation,
using a bit longer expression
that relies on CALCULATE
that does a CALCULATE of COUNTROWS of customers
where the customer name is not blank.
The result is totally identical.
And the code is a bit longer.
Being longer, in this case, is not bad
because it is a bit more clear
what are the intentions of the developer,
which is counting the numbers of customers
where the name is not blank.
So we slightly prefer the version
with CALCULATE to the version with COUNT
that despite being shorter,
it is a bit harder to read and understand.
You need to use COUNTX
instead of COUNT whenever you want
to count the number of expressions
that do not lead to a blank.
In this example, I'm using COUNTX over customer,
so I'm iterating over customers
and I'm counting the number of rows
where the result of this division is not a blank.
This division has no special meaning.
The only reason we are using it
is because sometimes this returns a blank.
So it provides some number.
COUNTX over customers provides 3,203
which is the number of customers
where this division is not a blank.
If you need to count the number of non-blank expression,
then in this case, COUNTX reads a bit better
if you compare it with the CALCULATE version.
That would be definitely too long.
Nevertheless, you can obtain the same result
using a COUNTROWS over a FILTER.
This COUNTROWS counts the number of rows
of the result of FILTER where FILTER
is filtering customers where the result
of the expression is not a blank.
Again, the code is a bit longer
but it is a bit easier to understand the intention
of the developer.
Moreover, you need to pay attention to strings
because when you count numbers,
so you're using COUNT over an expression
that returns a number,
then the only values which are not counted
are the blanks.
But in the very special case of strings,
there is a different behavior
between empty strings and blanks.
COUNT does not count blanks
but it counts empty strings.
Let's see that with an example.
First, I have the COUNT of customer names.
Then I created another calculation
that uses COUNTROWS of customers,
removing only the blanks.
And the third calculation removes the empty strings.
As you see from the result, count and count no blanks
they produce the very same result.
Whereas the version that does not count empty strings,
provides a different number.
COUNT considers in a different way blanks and empty strings.
Again, this is a good reason
why we prefer the version with CALCULATE.
If you rely on COUNT, you always need
to remember the special behavior with empty strings
whereas if you write the longer version
that is using CALCULATE,
then it will be easier to understand exactly
what you are counting by simply looking at the code.