COUNTROWS in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to count the number of rows in the 'Sales' table.

Power BI

TotalRows = [1](Sales)

Drag options to blanks, or click blank then click option'

ACOUNTROWS

BSUM

CAVERAGE

DMAX

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM or AVERAGE instead of COUNTROWS.

Trying to count rows with MAX function.

The COUNTROWS function counts the number of rows in a table. Here, it counts rows in the 'Sales' table.

2fill in blank

medium

Complete the code to count rows in the 'Customers' table where the 'Country' column equals 'USA'.

Power BI

USA_Customers = COUNTROWS(FILTER(Customers, Customers[Country] = [1]))

Drag options to blanks, or click blank then click option'

ACanada

BUSA

C"USA"

D'USA'

Attempts:

3 left

💡 Hint

Common Mistakes

Not using quotes around text values.

Using single quotes instead of double quotes.

Text values in DAX must be enclosed in double quotes. So, "USA" is correct to filter the 'Country' column.

3fill in blank

hard

Fix the error in the code to count rows in 'Orders' where 'Status' is 'Completed'.

Power BI

CompletedOrders = COUNTROWS(FILTER(Orders, Orders[Status] = [1]))

Drag options to blanks, or click blank then click option'

ACompleted

BCompleted"

C'Completed'

D"Completed"

Attempts:

3 left

💡 Hint

Common Mistakes

Using text without quotes.

Using single quotes instead of double quotes.

Mismatched or incomplete quotes.

The text value 'Completed' must be enclosed in double quotes in DAX. So, "Completed" is correct.

4fill in blank

hard

Fill both blanks to count rows in 'Products' where 'Category' is 'Bikes' and 'Price' is greater than 1000.

Power BI

ExpensiveBikes = COUNTROWS(FILTER(Products, Products[Category] = [1] && Products[Price] [2] 1000))

Drag options to blanks, or click blank then click option'

A"Bikes"

B"Cars"

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using single quotes for text.

Using wrong comparison operator for price.

Forgetting to use double ampersand && for AND.

Use "Bikes" with double quotes for text and the greater than symbol > to filter prices above 1000.

5fill in blank

hard

Fill all three blanks to count rows in 'Employees' where 'Department' is 'Sales', 'Age' is greater than 30, and 'Active' is TRUE.

Power BI

ActiveSalesOver30 = COUNTROWS(FILTER(Employees, Employees[Department] = [1] && Employees[Age] [2] 30 && Employees[Active] = [3]))

Drag options to blanks, or click blank then click option'

A"Sales"

B>

CTRUE()

DFALSE()

Attempts:

3 left

💡 Hint

Common Mistakes

Using FALSE() instead of TRUE().

Not quoting the text value.

Using && incorrectly.

Use "Sales" for the department text, > for age comparison, and TRUE() for the boolean active status.

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