POWER BI: Modeling

Counting Functions

This demo illustrates the different count functions you can write in Power BI. On how to summarize and count data without DAX visit: summarizing data in Power BI.

COUNT function

Counts up all the rows in a given column

Example Formula:

Define a measure to count all sales transactions:

                    number of sales = COUNT(TransactionData[Sales_Id])
	            

Note: It will not count blank or empty rows.

COUNTA function

Counts up all the rows in a given column.

Example Formula:

Define a measure to count all sales transactions:

                    number of sales = COUNTA(TransactionData[region])
	            

Note: It will not count blank or empty rows. Use COUNTA for text data types.

COUNTX function

Counts up the number of rows in an expression, evaluated over a table.

Example Formula:

Define a measure to count all sales transactions with revenue > $1000:

                    Number of sales w/ revenue > 1000 =         
                                        COUNTX(
                                        FILTER(TransactionData,TransactionData[Revenue] > 1000),
                                        TransactionData[Revenue])
	            

• A FILTER function is needed created a temporary table with transactions greater than 1000
• The Expression used is the last line TransactionData[Revenue]

Recall: Functions with an X at the end, have a table as the first parameter and an expression as the second parameter.

COUNTAX function

Counts up the number of rows in an expression, evaluated over a table. Use with non-numeric data

Example Formula:

Define a measure to count all rows where Ad Budget is "small" and Ad Source is "radio" in the advertising table:

                 CountData  = COUNTAX(
                         FILTER(
                           Advertising,
                           Advertising[Ad Budget] = "small" && Advertising[Ad Source] = "radio"),
                           Advertising[Ad Budget])
	            

• A FILTER function is needed created a temporary table (aka table expression) with only small and radio ads.
• The Expression used is the last line TransactionData[Revenue]

COUNTBLANK function

Counts up the number of rows in a given column, where the rows are empty.

Example Formula:

                   COUNTBLANK = COUNTBLANK(TransactionData[Region])
	            

COUNTROWS function

Counts up the number of rows in a table

Example Formula:

                   count sales = COUNTROWS(TransactionData)
	            

Power BI Demo:

The above graphs gives an example on how you can apply COUNTROWS to extract valuable business information.

How to Make:

• Select Matrix Visualization
• Drag count sales measure created above in to Values
• Drag product type into Rows
• Drag product id into Columns