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.