COUNTROWS in Power BI - Cell-by-Cell Formula Trace

Sample Data

This table lists products and their category. All products here belong to the 'Fruit' category.

Cell	Value
A1	Product
A2	Apple
A3	Banana
A4	Cherry
A5	Date
B1	Category
B2	Fruit
B3	Fruit
B4	Fruit
B5	Fruit

Formula Trace

COUNTROWS(FILTER(Table1, Table1[Category] = "Fruit"))

Step 1: FILTER(Table1, Table1[Category] = "Fruit")

Step 2: COUNTROWS(Table with rows [Apple, Banana, Cherry, Date])

Cell Reference Map

   A        B
1 Product  Category
2 Apple    Fruit  <-- included
3 Banana   Fruit  <-- included
4 Cherry   Fruit  <-- included
5 Date     Fruit  <-- included

The formula uses the Category column (B2:B5) to filter rows where the value is 'Fruit'. All rows from 2 to 5 are included.

Result

   A        B        C
1 Product  Category  Result
2 Apple    Fruit     4

The result 4 shows the count of rows where Category is 'Fruit'. It is placed in cell C2.

Sheet Trace Quiz - 3 Questions

What does the FILTER function return in this formula?

A All rows where Category is 'Fruit'

B Only the first row

C Rows where Product is 'Apple'

D No rows

Key Result

COUNTROWS counts the number of rows in a table or filtered table.

Practice

1. What does the COUNTROWS function do in Power BI?

easy

A. Filters rows based on a condition

B. Calculates the sum of values in a column

C. Returns the maximum value in a column

D. Counts the number of rows in a table or filtered table

Solution

1. Step 1: Understand the purpose of COUNTROWS

   The COUNTROWS function is designed to count how many rows exist in a given table or filtered table.

2. Step 2: Compare with other functions

   Unlike SUM or MAX, COUNTROWS does not perform calculations on values but counts rows.

3. Final Answer:

   Counts the number of rows in a table or filtered table -> Option D

4. Quick Check:

   COUNTROWS counts rows [OK]

Hint: Remember: COUNTROWS counts rows, not values or sums [OK]

Common Mistakes:

• Confusing COUNTROWS with SUM or MAX functions
• Thinking COUNTROWS filters data instead of counting
• Assuming COUNTROWS returns values from columns

2. Which of the following is the correct syntax to count rows in a table named Sales using DAX?

easy

A. COUNTROWS(Sales)

B. COUNTROWS = Sales

C. COUNTROWS[Sales]

D. COUNTROWS{Sales}

Solution

1. Step 1: Recall DAX function syntax

   DAX functions use parentheses to pass arguments, so COUNTROWS requires parentheses around the table name.

2. Step 2: Check each option

   COUNTROWS(Sales) uses correct syntax: COUNTROWS(Sales). Options A, B, and D use invalid syntax with brackets, equal sign, or braces.

3. Final Answer:

   COUNTROWS(Sales) -> Option A

4. Quick Check:

   COUNTROWS(table) uses parentheses [OK]

Hint: Use parentheses for function arguments in DAX [OK]

Common Mistakes:

• Using square brackets or braces instead of parentheses
• Writing an equal sign inside the function call
• Missing parentheses around the table name

3. Given a table Orders with 100 rows, and a filter applied to only include rows where Region = "West" which has 30 rows, what will COUNTROWS(FILTER(Orders, Orders[Region] = "West")) return?

medium

A. 30

B. 100

C. 0

D. Syntax Error

Solution

1. Step 1: Understand the FILTER function

   FILTER returns a table with only rows where Region equals "West", which is 30 rows.

2. Step 2: Apply COUNTROWS to filtered table

   COUNTROWS counts the rows in the filtered table, so it returns 30.

3. Final Answer:

   30 -> Option A

4. Quick Check:

   COUNTROWS of filtered 30 rows = 30 [OK]

Hint: COUNTROWS counts after filtering rows [OK]

Common Mistakes:

• Counting all rows ignoring the filter
• Assuming FILTER returns a number, not a table
• Expecting a syntax error due to FILTER usage

4. You wrote this DAX formula: COUNTROWS FILTER(Sales, Sales[Amount] > 100). What is the problem?

medium

A. FILTER should be replaced with CALCULATE

B. COUNTROWS cannot be used with FILTER

C. Missing parentheses around FILTER arguments

D. Sales[Amount] > 100 is invalid syntax

Solution

1. Step 1: Check function syntax

   FILTER is a function and requires parentheses around its arguments.

2. Step 2: Identify missing parentheses

   The formula misses parentheses after COUNTROWS and FILTER, it should be COUNTROWS(FILTER(Sales, Sales[Amount] > 100)).

3. Final Answer:

   Missing parentheses around FILTER arguments -> Option C

4. Quick Check:

   Functions need parentheses [OK]

Hint: Always use parentheses for all function calls [OK]

Common Mistakes:

• Omitting parentheses for nested functions
• Thinking COUNTROWS can't use FILTER
• Misusing comparison operators inside FILTER

5. You want to create a measure that counts how many customers made purchases over $500. The table Sales has columns CustomerID and Amount. Which DAX formula correctly counts distinct customers with purchases over $500?

hard

A. COUNTROWS(DISTINCT(FILTER(Sales[CustomerID], Sales[Amount] > 500)))

B. COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(Sales, Sales[Amount] > 500), "Customer", Sales[CustomerID])))

C. COUNTROWS(FILTER(Sales, Sales[Amount] > 500))

D. COUNTROWS(VALUES(Sales[CustomerID]))

Solution

1. Step 1: Understand the requirement

   We need to count distinct customers who have purchases over $500.

2. Step 2: Analyze each option

   COUNTROWS(FILTER(Sales, Sales[Amount] > 500)) counts all sales rows over $500, not distinct customers.
   COUNTROWS(DISTINCT(FILTER(Sales[CustomerID], Sales[Amount] > 500))) attempts to filter a column instead of a table, causing syntax error.
   COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(Sales, Sales[Amount] > 500), "Customer", Sales[CustomerID]))) correctly filters sales over $500, selects CustomerID column, then gets distinct customers and counts rows.
   COUNTROWS(VALUES(Sales[CustomerID])) counts all distinct customers regardless of purchase amount.

3. Final Answer:

   COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(Sales, Sales[Amount] > 500), "Customer", Sales[CustomerID]))) -> Option B

4. Quick Check:

   Filter, select column, distinct, then count rows [OK]

Hint: Filter, select column, distinct, then count rows [OK]

Common Mistakes:

• Counting rows instead of distinct customers
• Using DISTINCT incorrectly on filtered table
• Ignoring filtering before counting distinct