Counts the number of rows in the specified column that contain non-blank values or an expression that evaluates to a non-blank value, when evaluating an expression over a table.
|
Quick Coach Video |
What it does? Counts the number of rows in the specified column that contain non-blank values or an expression that evaluates to a non-blank value, when evaluating an expression over a table. Syntax COUNTX(<table>, <expression>) Returns An integer. |
What is the COUNTX Function?
The COUNTX function in DAX is like a thoughtful referee at a game—it doesn’t just count every entry; it evaluates a specific expression for each row and counts how many times that expression results in a non-blank value. Like how a referee has to consider each play and each penalty as their own. This makes it perfect for situations where you want to count based on conditions or calculations!
Example 1: Product Availability
Now, let’s say you manage a store and want to count how many products are currently in stock. Here’s your inventory data:
|
Product |
Quantity In Stock |
|
Apples |
30 |
|
Bananas |
0 |
|
Oranges |
25 |
|
Grapes |
50 |
To count how many products have a quantity greater than 0 without using the IF function, you can directly reference the Quantity In Stock column:
Products In Stock = COUNTX(Inventory, Inventory [Quantity In Stock])
Total Products Calculation:
- Apples: 30 (counted)
- Bananas: 0 (not counted)
- Oranges: 25 (counted)
- Grapes: 50 (counted)
So, how many products are currently in stock? That’s a total of 3 products! 🍏🍊🍇
Example 1: Sales Performance
Let’s step it up a notch to really see the power of COUNTX. Imagine you run a sales team and want to count how many sales representatives achieved sales greater than $10,000 last month. Here’s how your sales data might look:
|
Sales Rep |
Sales Amount |
|
Blake |
15,000 |
|
Angelica |
9,500 |
|
Terri |
12,000 |
|
Tricia |
8,000 |
To find out how many sales reps exceeded $10,000 in sales, you’d use the COUNTX function like this:
Sales Reps Over 10K = COUNTX(Sales,
IF(Sales[Sales Amount] > 10000,
Sales[Sales Rep]))
Total Sales Reps Calculation:
- Blake: 15,000 (counted)
- Angelica: 9,500 (not counted)
- Terri: 12,000 (counted)
- Tricia: 8,000 (not counted)
So, how many sales representatives exceeded $10,000? That’s a total of 2 sales reps! 💼 This works because the IF statement is assed every single row!
Conclusion: Count It Up!
The COUNTX function in DAX is your analytical ally for performing a targeted count based on specific criteria or calculations. Whether it’s sales performance or product availability, COUNTX helps you derive meaningful insights from your data!
So, what are you waiting for? Let’s start counting with COUNTX! 🥳