The COUNT() is an important function in writing the DAX formula in Power BI used. It is one of the time intelligence functions of DAX, which means it can manipulate data using time periods like days, weeks, months, quarters etc. and then use them in analytics.
We apply DAX to slice and dice the data to extract valuable information. To import data from different data sources and perform required transformations we need to know the use of Power Query. If you are curious to know the difference between Power Query and DAX, Here is an article you may be interested in.
Use of COUNT() in Power BI
The syntax for count function is very simple, we have to pass only the column name as argument like below
Measure = COUNT (Table_name [Column_name])Count function when applied on any column, it returns the count of cells containing numbers. So it returns only whole numbers and skips the blank cells. If any cell of a column does not contain anything (string, date or numerical) then the function returns blank.
Here is an example of the application of COUNT() on the data set I have on the rainfall of different Indian states. The dataset has three columns “SUBDIVISION” containing different ecological zones of the country, “YEAR” from 1901 to 2019 and “ANNUAL” containing rainfall in mm of the corresponding year.
Application of COUNT() in Power BI
I data I have collected from the web using the data scraping feature of Power BI desktop. Here is a glimpse of the dataset.
First, I have created a new measure using DAX (see here how can you create a new measure in Power BI). A measure has a default name “Measure” which I have changed to “Measure_count“.
Here you can see COUNT() is used to get the count of ANNUAL column cells having numbers. To see the result of COUNT() I have used a “Card”. The number “4090” in the card shows the cell count of the ANNUAL column having a number.
If we change the column and replace ANNUAL with SUBDIVISION, then the count function returns “4116”. This is because rainfall of all the subdivisions are not present in the ANNUAL column. We can check the difference and know how many subdivisions and year combinations do not have rainfall data.
The COUNTA() function
If a column consists of binary values like True and False, COUNT() fails to count them. To count such values COUNT() has another version which is COUNTA(). COUNTA() is for counting any logical value or text and also the empty cells of the column.
In this data set we dont have any logical values. If COUNTA() function is applied on the same columns i.e. ANNUAL and SUBDIVISION, the results are same as COUNT() gave.
The COUNTAX() function
For those columns which have values other than strings, digits, logical values, date like formulae then there is another useful variation of COUNT() which is COUNTAX(). It returns the count of non-blank rows evaluating the result of an expression on a table.
The DAX formula for COUNTAX() is:
COUNTAX ( <table>, <expression>)It also returns whole number and unlike COUNTA() function, it iterates through the cells of that column, evaluates the expression and returns count of nonblank rows.
Here is an example of the application of COUNTAX() on the same table. I have used this function to calculate the count of row number of ANNUAL column for a particular YEAR in the rainfall table. I have used the FILTER() function nested under COUNTAX() to filter the particular rows corresponding to the YEAR=1910 and 2010.
From the above figure we can see that the COUNTAX() function has returned two different whole numbers for two different years 1910 and 2010. This is because not all the SUBDIVISION has the record of annual rainfall for the year1910.