COUNTROWS in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

COUNTROWS with FILTER and ALL

Given a table Sales with columns Product and Quantity, what is the result of this DAX measure?

CountAllProducts = COUNTROWS(ALL(Sales[Product]))

Power BI

CountAllProducts = COUNTROWS(ALL(Sales[Product]))

A The total number of rows in the Sales table

B The total number of unique products in the Sales table

C The total number of products filtered by current context

D The total quantity summed across all products

💡 Hint

Think about what ALL does to the filter context on the Product column.

ALL removes any filters on the Product column, so COUNTROWS counts all unique products in the entire Sales table.

❓ visualization

intermediate

Best Visualization for COUNTROWS Result

You have a measure that counts the number of customers who made purchases this month. Which visualization best shows this single number clearly on a dashboard?

A A pie chart showing percentage of customers by region

B A clustered bar chart with customer names on the axis

C A card visual showing the count number

D A line chart showing customer count over time

💡 Hint

Think about the simplest way to show a single number clearly.

A card visual is best for showing a single numeric value clearly without distractions.

❓ data_modeling

advanced

COUNTROWS with RELATEDTABLE in a Star Schema

In a star schema, you have a Customers table and a related Sales table. You want to create a measure in Customers that counts how many sales each customer made. Which DAX measure is correct?

A SalesCount = COUNTROWS(RELATEDTABLE(Sales))

B SalesCount = COUNTROWS(Sales)

C SalesCount = COUNTROWS(RELATED(Sales))

D SalesCount = COUNTROWS(FILTER(Sales, Sales[CustomerID] = Customers[CustomerID]))

💡 Hint

RELATEDTABLE returns the rows in the related table for the current row in the Customers table.

RELATEDTABLE(Sales) returns all sales rows related to the current customer, so COUNTROWS counts those sales correctly.

🔧 Formula Fix

advanced

Why does this COUNTROWS measure return an unexpected zero?

Given this measure:

ActiveCustomers = COUNTROWS(FILTER(Customers, Customers[IsActive] = TRUE()))

It always returns zero even though some customers are active. What is the most likely cause?

A The IsActive column is text 'TRUE' not boolean TRUE

B FILTER is missing a closing parenthesis

C The Customers table is empty

D COUNTROWS cannot be used with FILTER

💡 Hint

Check the data type of the IsActive column and the filter condition.

If IsActive is text 'TRUE' instead of boolean TRUE, the filter condition fails and returns no rows, so COUNTROWS returns zero.

🧠 Conceptual

expert

COUNTROWS Behavior with Virtual Tables and Context

Consider this measure:

CountFiltered = COUNTROWS(FILTER(ALL(Sales), Sales[Quantity] > 10))

What does this measure return when used in a report filtered by a specific Product category?

A The count of all sales rows regardless of Quantity or product

B The count of sales rows with Quantity > 10 only for the filtered product category

C The total number of products with sales quantity > 10

D The count of all sales rows with Quantity > 10 ignoring the product filter

💡 Hint

Think about what ALL does to the filter context inside FILTER.

ALL removes filters on Sales table columns, so FILTER applies to all rows ignoring the product filter. COUNTROWS counts all rows with Quantity > 10 in the entire Sales 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