Differences COUNTAX and COUNTROWS

[ThomasSan]

Hi everyone,

I am currently doing the Master DAX course by SQLBI. In exercise 03.30, I attempted a solution with COUNTAX but this yielded a different result than the actual solution that used COUNTROWS. Apparently, I am missing something that I need to consider when applying COUNTAX.

The task in 03.30 is as follows:
Create a # CustWithChildren measure that computes the number of customers with children – only consider customers where Customer[Total Children] is greater than zero.

My DAX is as follows:

#CustWithChildren (Me) = 
countax(
customer,
Customer[Total Children] >0
)

You can find the PBI file for exercise 03.30 here .
Can someone please explain to me why my attempt did not arrive at the correct numbers? 

Thanks in advance!

[Ronak131290]

Hello ThomasSan,
Try these Dax 
CustWithChildren =
CALCULATE(
COUNTROWS(Customer),
Customer[Total Children] > 0
)
CountaX : This function counts the number of rows in a table where an expression evaluates to a non-blank value.

[ThomasSan]

Hi @Ronak131290 

thank you for your reply. I was more looking for someone to explain to me why COUNTAX did not work. As far as I see it, it should have worked as it is supposed to count the entries within the Customer table where the values within the column Total Children is larger than 0. Is that not meant with "where an expression evaluates to a non-blank value."?

[Ronak131290 · Accepted solution]

You can also use countax function 

CustWithChildren =
COUNTAX(
FILTER(Customer, Customer[Total Children] > 0),
Customer[Customer ID]
)
You need to filter table which is not contains total children is 0.

If you find these solutions accurate and helpful, please accept them as the solution to the problem.