Power BI – DAX Counting Functions

Many functions in the Data Analysis Expressions (DAX) return a table rather than a value. The table is used as an input for other functions, but it is not displayed.  For instance, get a table and count the number of distinct values it contains, or you could compute dynamic sums over filtered tables or columns.

Dataset used: 

The given data set contains information on the library supplies sold by a library manufacturer to different clients. All the relevant details like order date, client name, product name, product unit, etc. can be seen in it. 

Link: ‘SLS Order Details_Master‘, ‘SLS Order Details_Product Master‘

DAX Counting Functions

These functions calculate a (scalar) value such as the count for all rows in a column or table as defined by the expression. Measures are used in some of the most common data analyses.

DAX COUNT

This function only accepts a column as an argument. The following types of values are counted in rows using the COUNT function:

• Numbers
• Dates
• Strings

Syntax of DAX Count

COUNT(<column>)

Example:

Formula: Count = COUNT('SLS Order Detials_Master' [Book Date (dd-mm-yyyy)])

DAX CountA

Determines how many rows in the chosen column have non-blank values. The function returns a blank if it cannot locate any rows to count.

Syntax of the DAX CountA

COUNTA(<column>)

Example:

Formula: CountA = COUNTA('SLS Order Detials_Master' [Manufacturing Location])

DAX CountX

It evaluates an expression over a table and counts the number of rows that contain a number or an expression that evaluates to a number. It can be used to calculate the counts of rows based on certain expressions within FILTER.  Two arguments are needed for the COUNTX function. A table or any expression returning a table must always be the first argument. The column or expression that COUNTX searches for is the second input.

Syntax of the DAX CountX

COUNTX(<table, expression>) 

COUNTX(FILTER(<table, expression>, [column of which counts needs to be returned]))

Example:

Formula: CountX = COUNTX(FILTER('SLS Order Detials_Master', 'SLS Order Detials_Master'[Unit Price (INR/Unit)] > 1000), [Product Quanitity]))

DAX COUNTAX

It counts non-blank results when evaluating the result of an expression over a table. When determining the outcome of an expression over a table, the COUNTAX function counts results that are not blank. In other words, it functions just like the COUNTA function but is used to loop across table rows and count the rows where the supplied expressions return a result that is not blank.

Syntax of the DAX CountAX

COUNTAX(<table, expression>)

Example:

Formula: CountAX = COUNTAX('SLS Order Detials_Master', 'SLS Order Detials_Master'[Total amount (INR)])

DAX DistinctCount

It determines how many unique values there are in a column. This function only accepts a column as an argument. You can utilize columns with any kind of data in them. The function returns a BLANK if there are no rows to count; otherwise, it gives the number of distinct values.

Syntax of the DAX DistinctCount

DISTINCTCOUNT(<table>)

Example:

Formula: distinctcount = DISTINCTCOUNT('SLS Order Detials_Product Master'[Image])

DAX CountBlank

It analyzes the outcome of an expression across a table and counts outcomes that are not blank. A column is the only argument this function accepts. Columns can include any kind of data, but only blank cells are counted. Due to the fact that zero is a valid numeric value and not a blank cell, cells with the value zero (0) are not counted.

Syntax of the DAX CountBlank

COUNTBLANK(<column>)

Example:

Formula: countblank = COUNTBLANK('SLS Order Detials_Master'[Product Manufacturer])

DAX CountRows

It determines how many rows there are in the supplied table or a table that has been defined using an expression.

Syntax of the DAX CountRows

COUNTROWS([<table>])

Example:

Formula: COUNTROWS('SLS Order Details_Master')