Home » DAX » DAX – COUNT, COUNTA & COUNTX Functions

DAX – COUNT, COUNTA & COUNTX Functions

COUNT, COUNTA & COUNTX DAX Functions

Power BI DAX functions COUNT, COUNTA & COUNTX is used to counts the number of cells in a column, all functions comes under statistical 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:

  1. 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
  2. When the function finds no rows to count, it returns a blank.
  3. Blank values are skipped, if data type is Int.
  4. Blank values are not skipped, if data type is Text.
  5. 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:

  1. The only argument allowed to this function is a column.
  2. When the function does not find any rows to count, the function returns a blank.
  3. 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:

  1. The COUNT function counts rows that contain the following kinds of values:
    • Numbers
    • Dates
    • Strings
  2. Whenever the function finds no rows to aggregate, the function returns a blank.
  3. Blank values are skipped, if data type is Int.
  4. Blank values are not skipped, if data type is Text.
  5. 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.




Leave a Reply