# Power Pivot Principles: The A to Z of DAX Functions – COUNTAX

13 September 2022

*In
our long-established Power Pivot Principles articles, we continue our series on
the A to Z of Data Analysis eXpression (DAX) functions. This week, we look at COUNTAX.*

* *

*The
COUNTAX function*

Just when you thought this series had got the ax(e)…

This function counts the number of non-blank results when evaluating the result of an expression over a table. Not to be confused with the COUNTA function that similarly counts non-blank results albeit in one column, this function iterates through the rows in a table and counts rows where a specified expression gives a non-blank result.

The **COUNTAX **function
employs the following syntax to operate:

**COUNTAX(table, expression)**

The **COUNTAX** function has the following arguments:

**table:**this is required and represents the table containing the rows for which the expression will be evaluated**expression**: this is also required and represents the expression to be evaluated for each row of the table.

It should be further noted that:

- when the function finds no rows to aggregate, it returns a blank
- the
**COUNTAX**function counts cells containing any type of information, including other expressions. For example, if the column contains an expression that evaluates to an empty string, the**COUNTAX**function treats that result as non-blank. Usually, the**COUNTAX**function does not count empty cells but in this case the cell contains a formula, so it is counted - this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Consider the following **Sales **Table:

We may create the following measure:

Do note how this works. If the DAX formula had been

**=COUNTAX(Sales,
Sales[Target Achieved] = “Yes”)**

the answer would have been six [6]. This is because the **COUNTAX **function
counts the number of non-blank rows, not the number that contain “Yes”. Therefore, use **FILTER** to reduce the **Sales **Table to be just the rows where the target is achieved first (and then **COUNTAX** is counting the number of non-blank entries in the ‘**Sales Person**’ field.

Come back next week for our next post on Power Pivot in the *Blog** section. In the meantime, please remember we have training in Power Pivot which you can find out more about **here**. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs **here**.*