Power Pivot Principles: The A to Z of DAX Functions – COUNTX
4 October 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 COUNTX.
The COUNTX function
The COUNTXfunction counts the number of rows that contain a non-blank value or an expression that evaluates to a non-blank value, when evaluating an expression over a table.
It employs the following syntax to operate:
The COUNTXfunction has the following two arguments:
- table: this argument is required and represents the table containing the rows to be counted
- expression:this argument is also required. This is an expression that returns the set of values that contain the values you wish to count.
It should be further noted that:
- the function returns a whole number (integer)
- the COUNTX function takes two arguments: the first argument must always be a table, or any expression that returns a table; the second argument is the column or expression that is searched by COUNTX
- the COUNTX function counts only values, dates or strings. If the function finds no rows to count, it returns a blank
- if you wish to count logical values, use the COUNTAX function instead
- this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
An example could be:
=COUNTX(Sales, [Order Date])
This DAX expression would count all of the rows in the Salestable that have an Order Date.
More complex expressions may be used:
=COUNTX(Customer, DIVIDE(Customer[Estimated Salary], Customer[No of Dependents]))
This would count the results of the estimated salary per dependent in the Customer table.
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.