The COUNTAX function counts the number of rows in a table for which the evaluation of a certain expression returns nonblank values.
COUNTAX(
table,
expression
)
- table: Table containing the rows for which the expression will be evaluated.
- expression: Expression to evaluate for each row of the table.
The COUNTAX function returns an integer.
The function evaluates the expression included as the second argument for each of the rows in the indicated table, returning the number of nonblank results. Thus, if a cell contains an expression that does not return a Blank, even if the result is an empty text string it will be counted by the function..
If no rows are found, a Blank is returned. If rows are found but none satisfy the specified condition, a zero is returned.
If it is not necessary to count logical values or texts, the COUNTX function should be used.
The table argument can be either a table or a function that returns a table.
Unlike the COUNTROWS function, COUNTAX can include a measure as an argument, so it is possible to use the latter function to return the number of times the measure returns a nonblank value.
Suppose we have the following list of people:
Even when it is not visible in the image, the Nombre completo column is automatically calculated by concatenating the Nombre field, a blank space and the Apellido field.
If, under these conditions, we count the number of people by using the COUNTAX function:
Nº people = COUNTAX (People, People [Full name])
...the result is 5, since the row with the Nombre completo field empty is also counted as it includes an expression that returns an empty string, as we can see when taking this measure to a "card" type display:
In the previous scenario, to test that the function counts the rows that, being apparently empty, include an expression that returns a nonblank value, we modify the Excel table in which the data of the people is found so that the Nombre completo column concatenates the first and last names (with a blank space between both) if and only if the name is not an empty field:
= IF ([@ First name] <> ""; [@ First name] & "" & [@ Last name]; "")
We update the source data in Power BI and add a column that returns the length of the Nombre completo field:
We can check how, in this case, the row without first and last name contains a zero-length text string in the Nombre completo column.
If we now look at the "card" type display that contains the Nº personas measure, we see that it continues to show a 5:
