COUNTROWS in Power BI - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the COUNTROWS function do in Power BI?

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

intermediate

How do you use COUNTROWS with a filter in Power BI?

You use COUNTROWS with the FILTER function to count rows that meet specific conditions, like COUNTROWS(FILTER(Table, Condition)).

beginner

True or False: COUNTROWS can count rows in a physical table only.

False. COUNTROWS can count rows in any table expression, including filtered or calculated tables.

beginner

Write a simple DAX formula using COUNTROWS to count all rows in a table named 'Sales'.

COUNTROWS(Sales)

intermediate

Why is COUNTROWS useful in creating dynamic measures in Power BI?

Because it can count rows based on filters or conditions, helping to create measures that update automatically with user selections.

What does COUNTROWS return when used on a table?

A The number of columns in the table

B The number of rows in the table

C The sum of all numeric values in the table

D The average of all values in the table

COUNTROWS returns the number of rows in the specified table or table expression.

Which DAX expression counts rows where SalesAmount is greater than 100?

A COUNTROWS(FILTER(Sales, Sales[SalesAmount] > 100))

B COUNTROWS(Sales[SalesAmount] > 100)

C COUNTROWS(SalesAmount > 100)

D COUNTROWS(Sales, SalesAmount > 100)

You must use FILTER to create a table of rows meeting the condition, then COUNTROWS counts those rows.

Can COUNTROWS be used on a calculated table expression?

A Yes

B No

C Only if the table is physical

D Only with numeric columns

COUNTROWS works on any table expression, including calculated or filtered tables.

What will COUNTROWS return if the table is empty?

A Error

B 1

C 0

D Null

COUNTROWS returns 0 when the table has no rows.

Which scenario is a good use case for COUNTROWS?

A Counting total sales amount

B Calculating average price

C Finding maximum sales date

D Counting how many customers made purchases

COUNTROWS is useful to count how many rows meet a condition, like customers who made purchases.

Explain how COUNTROWS can be combined with FILTER to count specific rows in a table.

Think about filtering first, then counting.

Describe a real-life example where COUNTROWS would help in a sales report dashboard.

Imagine you want to know how many orders were made last month.

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