Power BI DAX functions COUNT, COUNTA & COUNTX is used to counts the number of cells in a column, all functions comes under Aggregation functions Dax categories.
1- COUNT DAX Function:
The COUNT function counts the number of cells in a column that contain non-blank values.
Syntax:
COUNT(<column>)
Description:
S no. | Parameter | Description |
1 | Column | The column that contains the values to be counted. |
Note:
- The only argument allowed to this function is a column. The COUNT function counts rows that contain the following kinds of values:
- Numbers
- Dates
- Strings
- When the function finds no rows to count, it returns a blank.
- Blank values are skipped, if data type is Int.
- Blank values are not skipped, if data type is Text.
- Does not support Logical values (TRUE/FALSE values).
Dataset format as below:
ID | Amount | Blank | Boolean | SalesDate | Name |
1 | TRUE | 29/5/2020 | A | ||
2 | 1000 | FALSE | B | ||
3 | 2000 | TRUE | 29/5/2020 | ||
4 | 1000 | FALSE | 28/5/2020 | C |
Let’s create measure for COUNT function with different-different columns
Count with Id column:
Count 1 = Count(SampleTable[ID]) -- Output = 4
Count with Amount column:
Count 2 = Count(SampleTable[Amount])-- Output = 3
Count with Blank column:
Count 3= Count(SampleTable[Blank])-- Output = Blank
Count with Sales Date column:
Count 4 = Count(SampleTable[SalesDate]) --- Output = 3
Count with Name column: Count 5 = count(SampleTable[Name])-- Output = 4 If data type is text or string count function not skipped blank value
2- COUNTA DAX Function:
The COUNTA function counts the number of cells in a column that are not empty.
Syntax:
COUNTA(<column>)
Description:
S no. | Parameter | Description |
1 | Column | The column that contains the values to be counted |
Note:
- The only argument allowed to this function is a column.
- When the function does not find any rows to count, the function returns a blank.
- The COUNTA function counts rows that contain the following kinds of values:
- Numbers
- Dates
- Strings
- Logical
Let’s create measure for COUNTA function with different-different columns.
COUNTA with Id column:
COUNTA 1 = COUNTA(SampleTable[ID]) --- Output = 4
COUNTA with Amount column:
COUNTA 2 = COUNTA(SampleTable[Amount])--- Output = 3
COUNTA with Blank column:
COUNTA 3= COUNTA(SampleTable[Blank])--- Output = Blank
COUNTA with Sales Date column:
COUNTA 4 = COUNTA(SampleTable[SalesDate]) --- Output = 3
COUNTA with Logical value:
COUNTA 5 = COUNTA(SampleTable[Boolean])--- Output = 4
COUNTA with Name column:
COUNTA 6 = COUNTA(SampleTable[Name])-- Output = 4
3- COUNTX DAX Function:
The COUNTAX function counts nonblank results when evaluating the result of an expression over a table.
Syntax:
COUNTAX(<table>,<expression>)
Description:
S no. | Parameter | Description |
1 | table | The table containing the rows for which the expression will be evaluated. |
2 | expression | The expression to be evaluated for each row of the table. |
Note:
- The COUNT function counts rows that contain the following kinds of values:
- Numbers
- Dates
- Strings
- Whenever the function finds no rows to aggregate, the function returns a blank.
- Blank values are skipped, if data type is Int.
- Blank values are not skipped, if data type is Text.
- Does not support Logical values (TRUE/FALSE values).
Let’s create measure for COUNTX function with different-different columns
COUNTX with Id column:
COUNTX 1 = COUNTX(SampleTable, SampleTable[ID]) --- Output = 4
COUNTX with Amount column:
COUNTX 2 = COUNTX(SampleTable, SampleTable[Amount])--- Output = 3
COUNTX with Blank column:
COUNTX 3= COUNTX(SampleTable, SampleTable[Blank])--- Output = Blank
COUNTX with Sales Date column:
COUNTX 4 = COUNTX(SampleTable, SampleTable[SalesDate]) --- Output = 3
COUNTX with Name column:
COUNTX 5 = COUNTX(SampleTable, SampleTable[Name])-- Output = 4
You can also used Filter DAX function with COUNTX :
Suppose you want to count no amount values where amount equal to 1000.
COUNTX with Filter = CountX( FILTER(SampleTable,SampleTable[Amount]=1000), SampleTable[Amount] ) Output = 2
Refer more DAX functions: DAX Tutorials
Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.