topic Differences COUNTAX and COUNTROWS in DAX Commands and Tips

Differences COUNTAX and COUNTROWS

ThomasSan — Thu, 01 Aug 2024 06:10:58 GMT

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!

Re: Differences COUNTAX and COUNTROWS

Ronak131290 — Thu, 01 Aug 2024 06:26:27 GMT

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.

Re: Differences COUNTAX and COUNTROWS

ThomasSan — Thu, 01 Aug 2024 06:34:53 GMT

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."?

Re: Differences COUNTAX and COUNTROWS

Ronak131290 — Thu, 01 Aug 2024 06:41:33 GMT

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.