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.
