How to Count Text Values in Power BI

Need to count how many times a specific word, category, or response appears in your Power BI data? You're in the right place. This is a fundamental task for anyone analyzing survey results, sales categories, or support ticket statuses. This guide will walk you through several methods for counting text values in Power BI, from simple drag-and-drop techniques to powerful DAX formulas.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Count Text Values in Power BI?

Before we get into the "how," let's quickly touch on the "why." Counting text isn't just about tallying words, it's about uncovering patterns and measuring frequency. Here are a few common scenarios where this skill is invaluable:

• Category Analysis: Finding out which product categories are the most popular or how many products belong to each one.
• Status Tracking: Counting the number of projects "Completed," "In Progress," or "Overdue."
• Survey Feedback: Tallying responses like "Satisfied," "Neutral," or "Dissatisfied" to gauge customer sentiment.
• Attribute Frequency: Counting how many customers are from a specific region or how many leads are assigned to each sales representative.

In each case, you're turning qualitative text data into a quantitative measure that you can use to build insightful charts and reports.

Method 1: The Quick Visual Count (No DAX Required)

Power BI often lets you find simple answers without writing a single line of code. If you just need a quick count, you can often do it directly within a visual.

Let's say you have a table named Sales with a column called Product Category. You want to see how many unique categories you have.

1. Select a Card visual from the Visualizations pane.
2. Drag the Product Category column from your Sales table into the 'Fields' well of the Card visual.
3. By default, Power BI might show you the 'First' category. Click the small downward arrow next to the field in the 'Fields' well.
4. A context menu will appear. From this menu, choose Count (Distinct).

The Card will now display the number of unique product categories. You can also select Count, which will show you the total number of rows (entries) in that column, including duplicates.

The difference is important:

• Count (Distinct): Counts the unique values. If you have "Electronics," "Books," "Electronics," "Home," it will count 3.
• Count: Counts every single item. For the same list, it will count 4.

This method is great for quick, high-level numbers, but for more flexibility and for use in more complex calculations, you’ll need to turn to DAX.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 2: Using DAX for Precise Control

DAX (Data Analysis Expressions) is the formula language of Power BI. While it may look intimidating at first, it gives you complete control over your calculations. Creating a DAX "measure" allows you to reuse the calculation across different visuals without having to configure it each time.

To create a new measure, go to the 'Home' or 'Modeling' tab in Power BI Desktop and click 'New Measure'. The formula bar will appear, ready for your DAX expression.

Counting All Text Occurrences (with Duplicates)

If your goal is to count every single entry in a column, including repeated values, you have a couple of solid options: COUNTA and COUNTROWS.

Using COUNTA

The COUNTA function counts the number of cells in a column that are not empty. It's purpose-built for counting values, including duplicates and both numbers and text.

Syntax: Measure Name = COUNTA('TableName'[ColumnName])

Example: Let's say you want to count the total number of sales transactions based on the Product Name column.

Total Transactions = COUNTA(Sales[Product Name])

This measure will count every row in the Product Name column that has a value. If you sold 500 laptops and 300 keyboards, this measure would return 800 (assuming those were the only two sales).

Using COUNTROWS

The COUNTROWS function does exactly what its name suggests: it counts the number of rows in a table. It's often used interchangeably with COUNTA for a full column count, as they will return the same result if the column has no blank values.

Syntax: Measure Name = COUNTROWS('TableName')

Example: Total Transactions = COUNTROWS(Sales)

Generally, COUNTROWS can be slightly more performant on very large datasets because it doesn't need to check the contents of a specific column. Use COUNTA if you need to be sure you're only counting rows where a specific column has a value, otherwise, COUNTROWS is a great default for counting total entries.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Counting Unique Text Values (Distinct Count)

This is one of the most common counting tasks. How many unique customers made a purchase? How many different products were sold? For this, the DISTINCTCOUNT function is your best friend.

Using DISTINCTCOUNT

DISTINCTCOUNT counts the number of unique, non-blank values within a column. It's the DAX equivalent of the 'Count (Distinct)' option from the visual menu.

Syntax: Measure Name = DISTINCTCOUNT('TableName'[ColumnName])

Example: You have thousands of sales transactions but want to know precisely how many different products you sell.

Number of Unique Products = DISTINCTCOUNT(Sales[Product Name])

If you have sold the same "Laptop Model X" 500 times, DISTINCTCOUNT will only count it once. This gives you an accurate count of your product catalog rather than the total volume of sales.

Conditional Counting with CALCULATE

What if you need to count text values that meet a specific condition? For instance, how many sales were in the "Electronics" category? Or how many survey responses were "Poor"?

This is where CALCULATE becomes essential. CALCULATE is one of the most powerful functions in DAX. It modifies the context in which a calculation is performed. Think of it as telling DAX: "Give me the result of this expression, but only for the data that meets these specific filtering conditions."

Syntax: Measure Name = CALCULATE(<expression>, <filter1>, <filter2>, ...)

Let’s look at some examples.

1. Counting a Specific Text Value

Goal: Count how many times the product "Superfast Laptop" was sold.

Here, we can combine CALCULATE with COUNTROWS:

Sales of Superfast Laptop = CALCULATE(COUNTROWS(Sales), Sales[Product Name] = "Superfast Laptop")

What this formula does: It counts the rows of the Sales table, but only after filtering the table to include just the rows where the Product Name is "Superfast Laptop".

2. Counting Unique Values with a Filter

Goal: Find out how many unique customers purchased an item from the "Accessories" category.

Combine CALCULATE with DISTINCTCOUNT:

Unique Customers in Accessories = CALCULATE(DISTINCTCOUNT(Sales[Customer ID]), Sales[Product Category] = "Accessories")

What this formula does: It filters the Sales table down to only rows where the Product Category is "Accessories". Then, within that filtered subset, it performs a distinct count of the Customer ID column.

3. Counting Text Based on a Partial Match

Goal: Count the number of products that have the word "Wireless" in their name.

For this, you can use a helper function like CONTAINSSTRING inside the filter argument of CALCULATE. When using more complex functions like this, it’s best to wrap the logic in a FILTER function.

Count of Wireless Products =
CALCULATE(
    COUNTROWS(Sales),
    FILTER(
        Sales,
        CONTAINSSTRING(Sales[Product Name], "Wireless")
    )
)

What this formula does: The FILTER function iterates over every row of the Sales table. The CONTAINSSTRING function checks if "Wireless" exists within the Product Name for each row. Only the rows that return TRUE are passed to COUNTROWS to be counted.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Final Thoughts

You now have a complete toolkit for counting text data in Power BI. You can perform quick counts directly in visuals for fast insights or build robust and reusable DAX measures using functions like COUNTA, DISTINCTCOUNT, and the powerful CALCULATE for more complex, conditional counts.

Mastering these functions is a huge step in moving from a beginner to an intermediate Power BI user. We know that learning the nuances of DAX and data modeling can take a long time, which is why we built our tool around natural language. With Graphed, you can securely connect your data sources and simply ask questions like, "How many completed projects did each team have last quarter?" and get the visualization and answer instantly. We connect your data so you get all the analytical power without the steep learning curve.