Power Bi count function – DAX Examples

In this Power bi tutorial, we will discuss the Power BI Count function with examples. And also we will discuss the below points.

• Power bi COUNT function
• Power bi COUNTA function
• Power bi COUNTAX function
• Power bi COUNTBLANK function
• Power bi COUNTROWS function
• Power bi COUNTX function
• Power bi DISTINCTCOUNT function
• Power bi DISTINCTCOUNTNOBLANK function
• When to use COUNT, COUNTA, and COUNTX function
• What is the difference between COUNT and DISTINCTCOUNT function
• DAX countrows vs count
• Power bi DAX Count function example

Power Bi COUNT function

The Power Bi COUNT function counts the number of cells in a column that nonblank values. And the return value is a whole number.

This function allowed the only argument i.e column. The Power BI COUNT function counts rows that contain the following kinds of values:

• Numbers
• Dates
• Strings

When the count function finds no rows to count, it returns a blank. Logical values are not supported by this function.

The syntax for the COUNT function in Power BI:

= COUNT(<column>)

For example:

Read Power BI MAX and MIN function with Examples

Power Bi COUNTA function

The Power Bi COUNTA function counts the number of cells in a column that are not empty.

When the Power BI COUNTA function does not find any rows to count, the function returns a blank.

The syntax for COUNTA function in Power BI:

=COUNTA(<column>)

For example:

This is how to use the Power Bi COUNTA function.

Read Power BI average function with Examples

Power Bi COUNTAX function

The Power bi COUNTAX function counts non-blank results when evaluating the result of an expression over a table.

The COUNTAX function in Power BI works similar to the COUNTA function, but it is used to iterate through the rows in a table and count rows where the specified expressions result in nonblank rows.

The syntax for Power Bi COUNTAX function:

=COUNTAX(<table>,<expression>)

For example:

This is how to use the Power Bi COUNTAX function.

Read Power BI DAX Logical functions

Power Bi COUNTBLANK function

The Power bi COUNTBLANK function counts the number of blanks cells in a column. Its return type is the whole number and if no rows are found that meet the condition, blanks are returned.

When there are no rows to aggregate, the COUNTBLANK function return blank, and if the COUNTBLANK function finds no blank, the result will be zero

The syntax of the COUNTBLANK function

COUNTBLANK= COUNTBLANK(<column>)

For example:

This is how to use the Power Bi COUNTBLANK function.

Read Power BI DAX SUM and SUMX function

Power Bi COUNTROWS function

Power Bi COUNTROWS function counts the number of rows in the specified table or in a table defined in an expression. It returns a whole number.

The COUNTROWS function in Power BI can be used to count the number of rows in a specified table, but more often it is used to count the number of rows that result from filtering a table.

If there are no rows to aggregate then the functions return a blank.

The syntax of power bi count rows function:

COUNTROWS= COUNTROWS(<[table]>)

For example:

This is how to use the Power Bi COUNTROWS function.

Read Power bi measure count with filter

Power bi COUNTX function

The power bi COUNTX function counts the number of rows that contain a nonblank value or an expression that evaluates a non-blank value when evaluating an expression over a table.

The return value of the COUNTX function is an integer.

This function takes two arguments, whereas the first argument must be a table or any expression that returns a table, and the second argument is the column or an expression that is searched by COUNTX.

This function counts only values, dates, and strings.

The syntax for Power Bi COUNTX function:

Countx = COUNTX(<table>,<expression>)

For Example:

This is how to use the Power bi COUNTX function.

Read How to get selected value from Slicer in Power BI

Power bi DISTINCTCOUNT function

The Power Bi DISTINCTCOUNT function counts the number of distinct values in a column. The return value of this function is the number of distinct values in a column.

The only argument allowed to this function is a column. This function can use any column for any type of data. DISTINCTCOUNT function includes the blank values.

The syntax for Power Bi Distinct count function:

Distinct Count = DISTINCTCOUNT(<column>)

For example:

This is how to use the Power bi DISTINCTCOUNT function.

Read Power BI Measure multiply

Power bi DISTINCTCOUNTNOBLANK function

The Power Bi DISTINCTCOUNTNOBLANK counts the number of distinct values in a column.

It is similar to the DISTINCOUNT function, but the DISTINCTCOUNTNOBLANK does not include a blank value.

The syntax for Power bi DISTINCTCOUNTNOBLANK function:

= DISTINCTCOUNTNOBLANK (<column>)

For example:

This is an example of the Power bi DISTINCTCOUNTNOBLANK function.

Read Power bi sort by measure

When to use COUNT, COUNTA, and COUNTX function

Here we will see when to use the COUNT, COUNTA, and COUNTX function for example.

We will use the below sample table to learn when to use the Power BI COUNT, COUNTA, and COUNTX function.

COUNT DAX Function

We will use the Count function when:

• Data type is number, date and text
• Does not support logical values

Let’s see the COUNT DAX function with a few examples.

COUNT() WITH number data type

Here we will see how to count the rows of the id column using the measure in power bi desktop.

To count the rows of the id column, write the below measure:

Count = COUNT(Data[ID])

COUNT() with date datatype

Here we will see how to count the rows of sales data using the measure in power bi.

To count the cells in the sales date column, write the below measure

Count = COUNT(Data[Sales Date])

COUNT() with text data type

Here we will how to count the number of cells in the product name column using the measure in power bi desktop.

To count the number of cells in the product column, write the below measure:

Count = COUNT(Data[Product name])

COUNTA DAX function

We will use the Power BI COUNTA function when

• The data type is Number, text and Dates data type
• It supports the boolean type

Let’s see the COUNTA function for example.

COUNTA() with number data type

Here we will see count the number of cells in the amount column using the measure in power bi desktop.

To count the number of cells in the amount, write the below measure

Count = COUNTA(Data[Amount])

COUNTA() with text data type

Here we will see how to count the number of cells in the product column using the measure in power bi desktop.

To count the number of cells in the product column, write the below measure:

CountA = COUNTA(Data[Product name])

COUNTA() with dates data type

Here we will see how to count the sales dates column using the measure in power bi.

The count the number of cells in the date of the sales column, write the below measure:

CountA = COUNTA(Data[Sales Date])

COUNTA() with boolean data type

Here we will see how to count the Boolean column using the measure in power bi desktop.

To count the number of cells in the boolean column, write the below measure:

CountA = COUNTA(Data[Boolean])

COUNTX Dax function

We will use the COUNTX function when

• The data type is Number, dates and Text data type
• It doesnot support logical values
• We want to use the filter condition

Let’s see the COUNTX function with example

COUNTX() with number data type

Here we will see how to count the Amount column using the measure in power bi desktop.

To count the Amount column, write the below measure:

CountX = COUNTX(Data, Data[Amount])

COUNTX() with Date data type

Here we will see how to count the sales date column using the measure in power bi desktop.

To count the sales date column, write the below measure

CountX = COUNTX(Data, Data[Sales Date])

COUNTX with text data type

Here we will see how to count the cells in the product name column using the measure in power bi desktop.

To count the cells in the product name, write the below measure:

CountX = COUNTX(Data, Data[Product name])

COUNTX() with filter condition

Here we will see how to Count the sum amount as 1000 using the measure in power bi desktop.

To count the sum amount as 1000, write the below measure:

CountX = COUNTX(FILTER(Data, Data[Amount]=1000), Data[Amount])

Read Power bi measure divide + 8 examples

Difference between COUNT and DISTINCTCOUNT function

Here we will see the difference between COUNT() and DISTINCTCOUNT FUNCTION In Power Bi.

• COUNT(): Counts the number of cells in a column, the column that contains the values to be counted.

For example: Here we will count the number of Sales IDs from the sales table.

It will only count the cells having value and if the cell is blank, it gets skipped.

Count of id = COUNT(Data1[Sales Id])

• DISTINCTCOUNT(): This function counts the unique values in the column.

For example: Here we will count distinct Sales IDs from the sales table.

So, for this write the below measure :

Distinct Count of id = DISTINCTCOUNT(Data1[Sales Id])

This is the difference between the COUNT and DISTINCTCOUNT function in Power BI.

Read Power BI Measure Date

DAX countrows vs count

Here we discuss about COUNTROWS VS COUNT function in the Power bi.

We can use the COUNT function to count the column values and also you can use the COUNTROWS function to count table rows.

Both the function will achieve the same result, providing that the counted column contain no blanks

Let’s discuss both the function with an example, for this we will use the below sample table:

Count: Here we will count the number sales id, so for this write the below measure

Count of id = COUNT(Data1[Sales Id])

• Count function provides the granuality of the table is one row per sales id.
• And the sales id does not contain blank, then the measure will return a correct result.

COUNTROWS: Here we will count the rows of the table, so for this write the below measure

CountRows = COUNTROWS(Data1)

Both the function gives same result, but there are three reason COUNTROWS function is preferable because:

• It’s more efficient, so it will perform better.
• The intention of formula of COUNTROWS function is clearer to the point of being self describing
• This function doesnot consider blank present in any column of the table.

Read Power BI Measure Filter

Power bi DAX Count function examples

Here we will see different examples of the Power BI COUNT, COUNTROWS, COUNTX, and DISTINCTCOUNT using power bi desktop.

Example 1: Power bi dax count number of occurrence

Here we will count the number of occurrence of sales id using Calculated column in power bi desktop.

We will use the below sample table, to calculate the number of occurrence.

To calculate the number of occurence of Sales id, we will use the count function

• Load the data using get data
• Click on the Modelling tab -> New column.
• Then write the below DAX formula

Occurence = 
COUNTX (
    FILTER ( Data1, EARLIER ( Data1[Sales Id] ) = Data1[Sales Id] ),
    Data1[Sales Id]
)

Example 2: Count the distinct values in a column

Here we will count the distinct item id whose status is open using measure in power bi desktop.

Here we will use the below sample, having three columns item id, item and status

To count the distinct item id whose status is open, for this write the below measure:

CountItem = CALCULATE (
    DISTINCTCOUNT (Sheet[Item Id] ),(Sheet[Status]="Open"))

Read Power bi measure switch statement with examples

Example 3: Power bi count rows by month

Here we will see how to count employee by month using measure in power bi desktop.

We will use the two sample table, one is hr table and other one is date table.

So the HR table contains three columns i.e name, hire date and terminate date

Date table: It is a calculated table, you can write the below DAX formula

Dates = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2018, 07, 30 ) ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "Month #", MONTH ( [Date] ),
    "Year Month", YEAR ( [Date] ) & " " & FORMAT ( [Date], "MMMM" ),
    "Year Month #", YEAR ( [Date] ) * 100 + MONTH ( [Date] )
)

• Load the data using get data
• Click on the new measure from the ribbon to head count of employee by month
• Then write the below measure:

Head Count of employee = 
COUNTROWS (
    FILTER (
        'HR Table',
        DATE ( YEAR ( 'HR Table'[Hire date] ), MONTH ( 'HR Table'[Hire date] ), 1 )
            <= FIRSTDATE ( Dates[Date] )
            && (
                ISBLANK ( 'HR Table'[Terminated date] )
                    || EOMONTH ( 'HR Table'[Terminated date], 0 ) >= LASTDATE ( Dates[Date] )
            )
    )
)

Example 4: Calculate the number of sales

Here we will see you to calculate the number of sales using COUNT() in power bi desktop.

We will use the below sample table having 4 column i.e. Sales id, sales, dales person, and region.

To calculate the number of sales id, write the below measure:

Number of sales id = COUNT(Data1[Sales Id])

You may also like the following Power BI tutorials:

• Countif function in Power BI Measure
• How to create a Measure based on Slicer in Power BI
• Power bi show items with no data
• Power bi date format
• Microsoft Power BI KPI Visual

In this Power bi tutorial, we learned about the different aggregation functions in Power BI i.e. COUNT, COUNTA, COUNTX, COUNTAX, COUNTROWS,COUNTBLANK, DISTINCTCOUNT, and DISTINCTCOUNTBLANK.

And also we discuss the below points:

• Power bi COUNT function
• Power bi COUNTA function
• Power bi COUNTAX function
• Power bi COUNTBLANK function
• Power bi COUNTROWS function
• Power bi COUNTX function
• Power bi DISTINCTCOUNT function
• Power bi DISTINCTCOUNTNOBLANK function
• When to use COUNT, COUNTA, and COUNTX function
• What is the difference between COUNT and DISTINCTCOUNT function
• DAX countrows vs count
• Power bi DAX Count function example