COUNTROWS in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to know how many sales transactions happened each month to understand sales activity volume.

📊 Data: You have a sales data table with columns: TransactionID, Date, Product, Quantity, and SalesAmount.

🎯 Deliverable: Create a report showing the count of sales transactions per month.

Sample Data

TransactionID	Date	Product	Quantity	SalesAmount
1001	2024-01-05	Shirt	2	40
1002	2024-01-15	Pants	1	50
1003	2024-02-03	Hat	3	30
1004	2024-02-20	Shirt	1	20
1005	2024-03-10	Shoes	1	80
1006	2024-03-15	Shirt	2	40
1007	2024-03-25	Pants	1	50
1008	2024-04-05	Hat	2	20
1009	2024-04-18	Shoes	1	80
1010	2024-04-22	Shirt	1	20

1

Step 1: Create a new calculated column to extract the month and year from the Date column for grouping.

MonthYear = FORMAT('Sales'[Date], "YYYY-MM")

Expected Result

A new column 'MonthYear' with values like '2024-01', '2024-02', etc.

2

Step 2: Create a measure to count the number of sales transactions for each month.

MonthlyTransactionCount = COUNTROWS('Sales')

Expected Result

A measure that counts all rows in the Sales table filtered by the current month context.

3

Step 3: Build a table visual with 'MonthYear' on rows and the 'MonthlyTransactionCount' measure as values.

Configure table visual: Rows = 'Sales'[MonthYear], Values = MonthlyTransactionCount

Expected Result

Table showing each month and the count of transactions: 2024-01 = 2, 2024-02 = 2, 2024-03 = 3, 2024-04 = 3

Final Result

MonthYear | Transactions
------------------------
2024-01   | 2
2024-02   | 2
2024-03   | 3
2024-04   | 3

✓January and February each had 2 sales transactions.

✓March and April had higher sales activity with 3 transactions each.

✓Sales volume increased in March and April compared to the first two months.

Bonus Challenge

Create a measure to count only transactions where Quantity is greater than 1 for each month.

Show Hint

Use COUNTROWS with FILTER to count rows where 'Sales'[Quantity] > 1.

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