If you are working with data to provide actionable insights, a question often to deal with is, How many times does a specific value appear in the dataset?

Counting how many times a value appears in Power BI is a simple yet powerful way to get essential insights about the patterns, trends, and overall distribution of the information from your dataset. The beauty of this tool is there are various ways to get this done and through this article, I will walk you through all possible ways.

I am so excited and hope you are too! Let’s make it COUNT! 😉

The Dataset

The dataset I prepared for this demo is Product Data, as shown above. This table contains four self-explanatory columns: ID, Product_Category, Price, and Status. There are 500 rows in this demo dataset, which should be sufficient to walk you through this entire article.

Load the data in Power BI

To load this data in Power BI, follow the steps below:

1. Open the Power BI Desktop and navigate toward the Home tab
2. Click on the Get data dropdown and select the Excel workbook option.

3. Please navigate to the location where the Product Data Excel file is stored locally and select it. Click on the Open button to open it in Power BI.
4. Select the sheet containing the data table inside the Navigation window that opens up and click on the Load button to import this data into Power BI.

Pro Tip: You can also have the Excel workbook option to get data as soon as you open the blank Power BI report on the local system. Alternatively, you can click on the More… option from the Get data dropdown, and there connecting to Excel source option is available.

Now, you are ready to play around with different methods to count how many times a value (text or number) appears in Power BI.

Let’s first see all the Power Query ways to get this done.

Using Power Query Filter and Count Rows

This is another most straightforward approach where you can find out how many times a single value appearce in Power BI through Power Query Editor by filtering the column for that specific value and getting the count below.

1. Inside the Power Query Editor, select the Product_Category column and click on the right-side dropdown.
2. By default, all values are selected. Untick all others and keep “Clothing” selected.
3. Finally, click on the OK button.
4. You will see the count 149 at the extreme left bottom of the Power Query Editor.

This method applies to all the values from any column in your query table.

Using Power Query Table.SelectColumn and Table.RowCount

One of the fundamental methods for counting occurrences in Power BI using the Power Query involves using the combination of Table.SelectColumn and Row.Count functions. This approach allows you to filter the data to include only rows containing the specified value and then count the number of occurrences.

First thing first, let’s launch the Power Query Editor by clicking on the Transform data option from the Home tab.

You will already see the Product Data table as a query in the Power Query Editor. For now, you will need to create a new blank one.

Go to the Home tab. Click on the New Source dropdown and select the Blank Query option. Rename it as Count_value_occurrence.

Next, go to the Home tab and click on the Advanced Editor (grouped under the Query section) to launch the full-fledge M-code editor, where you can edit or add a new code related to a query.

Write the following code inside the Advanced Editor window and click Done.

let
    source = #"Product Data",
    columnToCount = "Product_Category",
    valueToCount = "Clothing",

    filteredTable = Table.SelectColumns(source, {columnToCount}),
    filteredRows = Table.SelectRows(filteredTable, each [Product_Category] = valueToCount),

    count = Table.RowCount(filteredRows)
in
    count

When you click Done, you will see 149 as the count of occurrence of value Clothing inside the Product_Category column.

Explanation

• The first line of code creates a variable named source and assigns it the value of the table named Product Data. The # symbol indicates that Product Data is a table.
• You create a columnToCount variable on the second line and assign the value Product_Category. It is a column based on which count will be given.
• You know the column to count data from, but you will also need a specific value to count, won’t you? For this purpose, you create a variable valueToCount with the value Clothing.
• Then, you use the Table.SelectColumns function allows you to create a new table named filteredTable that includes only the specified column, Product_Category.
• The Table.SelectRows function filters filteredTable to include only the rows where the value in the Product_Category column matches the specified value, Clothing. The result is stored in the filteredRows variable.
• The Table.RowCount function calculates the number of rows in the filteredRows table, representing the count of occurrences of the value (“Clothing”) in the Product_Category column.
• Finally, the value of this entire expression is stored inside the count variable,

This is how you can calculate the count of how many times a specific value appears in Power BI using Power Query M-Code Functions.

Note: This method and all the ones discussed above only give you the count of value occurrence for one single value at a time. You have to specify individual values one time each every time. This might not always be convenient, and that’s why the following methods are essential, giving you the count for all values simultaneously.

Using Power Query Text.Contains

The Text.Contains function in Power Query is specifically designed to search for a substring within a string. Imagine a paragraph out of which you want to find out a substring. This function does that for you.

With these capabilities, you will leverage this function in combination with Row.Count function to find a count of a specific value from a given column. Please note that this method only works with the Text columns. Let’s see how.

Inside Power Query Editor, click on the New Source dropdown in the Home tab and select the Blank Query option to add one to your model. Rename it as per your convenience.

Launch the Advanced Editor through the Home tab placed inside the Query section.

After that, add the following M-code inside the Advanced Editor window that opens up. Once done, click on the Done button.

let
    Source = #"Product Data",

    CountofClothing = Table.RowCount(Table.SelectRows(Source, each Text.Contains([Product_Category], "Clothing")))
in
    CountofClothing

And, Bingo! You will see the count 149 for Product_Category “Clothing” as a single value.

Explanation

• As you already know by now every series of expressions in M-Code is enclosed within a let and a in clause.
• The second line defines a variable named “Source” and assigns a reference of the Product Data table to it through #”Product Data.” This adds a step inside Power Query to access the table.
• Inside the third line, the Table.SelectRows function returns all the rows from the Source table where Product_Category contains a string “Clothing.” The Text.Contains function is used for that purpose.
• Finally, the entire formula above is nested inside the Table.RowCount function to count all those rows where Product_Category contains a string “Clothing.” This entire formula is then assigned to a variable CountofClothing.
• Finally, this CountofClothing variable needs to be returned, and that’s why it is recalled after the in.

Warning: This method’s uniqueness is that it targets values with a specific data type – string. If you try to implement this entire M-code on a column that is not a string, you will definitely get an error, as shown above. The Price column is a numeric column, and the system fails to convert a specific value to a string, for example, 10.99 in this case.

Using the Power Query List.Count and List.Select

There is literally so much with Power Query that you can do a simple task in four, five, or more unique ways. One such task is counting value occurrence. There are literally so many methods there that I feel dizzy while writing them down. But the show must go on!

The following method for this task is a List.Select in combination with List.Count to generate a count of how many times a value appears in Power BI.

The List.Select function works as a filtering criteria that select all the items from a list (in this case you will provide a column which will be considered a list) that follows a specific criteria.

This entire list of values following specific criteria will then be used as an input to the List.Count function, which, as you might have guessed, already generates the value count.

Please create a new Blank Query inside Power Query Editor and rename it Count Value Using ListCount and ListSelect. Even though a name can be anything for the query, it is always a best practice to give an intuitive name so that you can understand later what it does.

Open the Advanced Editor through the Home tab and write the following code inside the window that opens up. Click on the Done button to update the query.

let
    
    Source = #"Product Data",

    CountofClothing = List.Count(List.Select(Source[Product_Category], each _ = "Clothing"))
in
    CountofClothing

Once you click the Done button, you will see the count of value clothing on the screen, as shown above.

Now, it’s time to decode the magic. Let’s see how these functions do this task.

Explanation

• The Source variable creates a reference to the table Product Data through #”Product Data.”
• The List.Select function uses this reference to filter out all the rows where Product_Category is “Clothing.”
• This formula is then enclosed within the List.Count function, which counts all the occurrences of Clothing inside Product_Category and returns a number you see on the screen.

Note: Unlike the previous method, this one works with columns of any data type and is not explicitly limited to the string columns. This gives you an added benefit for using this method over the previous one.

Using Power Query Custom Conditional Column

As you might have guessed by now, counting how often a specific value appears within a column is a common analytical task to get insight from your data. This section focuses on a method utilizing conditional columns to calculate the count of a desired value efficiently. This approach is instrumental when dealing with categorical data, such as product categories or status indicators. But it isn’t limited to those and can be used on any data.

To get this done using a Custom column with Conditional Count, first, create a Blank Query through the New Source dropdown and click on the Advanced Editor option through the Home tab inside Power Query Editor.

Inside the Advanced Editor window, copy and paste or write the following M-Code.

let
    Source = #"Product Data",
    #"Added Conditional Column" = Table.AddColumn(Source, "isClothing", each if [Product_Category] = "Clothing" then 1 else 0),
    CountofClothes = Table.AddColumn(#"Added Conditional Column", "CountClothing", each List.Sum(#"Added Conditional Column"[isClothing])),
    CountClothing = CountofClothes{0}[CountClothing]
in
    CountClothing

Once completed, click on the Done button to complete the code. You will see that the count 149 is visible.

Explanation

• The first line refers to the initial data source. It establishes the connection to the Product Data table. It stores its content in the Source variable so that every time you want to utilize the table, it can be referred to through the variable name.
• Here, in the second line, you create a conditional column named “isClothing.” Each row checks if the value in the “Product_Category” column is “Clothing.” If true, it assigns 1; otherwise, it assigns 0. So now, you have a list of values with 1 or 0, depending on the value of each row. All these steps are stored in a variable named Added Conditional Column.
• The third line extends the table by adding a new column named “CountClothing.” The values in this column represent sum of all 1’s from the “isClothing” conditional column. It uses List—s—s—sum to calculate the sum. So, in this step, the newly created column will always have the exact value count in all rows.
• Finally, the last line of the script extracts the value of the count from the first row of the “CountClothes” column. This single value represents the overall count of occurrences where the “Product_Category” is “Clothing.” The {0} represents the first row of the column.

Throughout the sections until now, whatever Power Query Functions you have utilized to generate the count of value occurrence, each variable inside the M-Code you write represents a step inside the APPLIED STEPS section.

For example, if you see this section for Count Value using Conditional column query, it would look like the one above. If you look at the names of each step, those are the same as the variable names in the M-Code.

Then, it all starts to make sense that on each line of code, you are adding a step that transforms your data inside the Power Query Editor so that you can reach the final output.

If you click on each of these steps individually, you will see the transformations happening on the original data step by step. By default, the last step is always selected in Power Query, representing the latest step or transformation applied.

Note: This method is very flexible because it allows user to change the conditional at their convenience.

Warning: This method works best on small datasets, but it will slow the report down when you have a considerable dataset where rows are in millions or so.

Imagine a step that iterates through all millions of rows to check whether the specific condition is met and then assigns a value of 1 or 0 depending on that. This is a considerable task for large datasets.

Using Power Query Group By and Aggregation

What if you have customized groups of distinct values from a specific column and then get the total count of occurrence of those values?

The Group By functionality in Power Query allows you to create distinct value groups based on a specific column. On top of that aggregation function named Count Rows then summarizes the data within those groups, returning the counts for each distinct group within the column. This functionality lets you count how many times a particular value appears from that column.

1. Inside the Power Query Editor, go to the Transform tab and click on the Group By option.
2. Inside the Group By window that opens up, select the column Product_Category.
3. Add the column name as Product Category Count inside the New column name textbox. Under this column, you will see the count for each category.
4. The Operation should be Count Rows which you can select from the dropdown.
5. Finally, click the OK button to apply this aggregation on the Product_Category column.

Now, you see that the new table now contains four rows which are distinct product categories with their count inside a new column.

Pro Tip: You can select the required column first and then go to the Group By option to make it appear directly in the dropdown. In this way, you don’t need to select the column inside the Group By window.

Pro Tip 2: You can also select the Group By option through the Home tab inside Power Query Editor.

Note: It is always recommended to create a copy of your original dataset because the Group By with aggregation method permanently alters the table itself, and creating a copy of the dataset saves you from losing the original data.

Using Power Query Column Profile

Inside the Power Query Editor, you have a Column profile option, which gives you a visual representation of column statistics for a selected one. Within a minute allows you to understand the distribution, spread, etc. The same tool can also be used to see how many times a value appears in your dataset.

To enable Column profile –

1. In Power Query Editor, click on the View tab.
2. Check the Column profile box.

A new detailed pane will open up below your data table. You select a specific column, and information regarding that column will be shown there. You can see all information segregated into two sections named Column Statistics and Value Distribution.

If you select the column Product_Category and then navigate to the Value Distribution section, you should see bars representing the count of each occurrence of the distinct values from that column.

This is one way to see the count of values in Power BI using the simple yet informative Column profile option.

Here ends the first section of this article where I tried to navigate through all possible (There might be other ways, but those will be on similar lines) Power Query ways to count how many times a value appears in Power BI.

The next section of this article will give you a more enhanced way where you can count values directly through visuals or by creating fantastic DAX measures to represent them.

Using the Clustered Column Chart, Visual

The clustered column chart is a popular Power BI visual that displays data in vertical columns or bars based on the grouped categories. It is often used to show how values flow across each category. You will simultaneously use the same chart to represent the count of values for each Product_Category.

Inside the Power BI, go to the Report view and select the Clustered column chart option from the Visualizations pane.

1. On the X-axis, drag and drop the Product_Category column.
2. Again, drag and drop the Product_Category column on the Y-axis to show the count for each category.

You will see a chart as shown above, where the height of each column represents the count of the respective category. However, to show these counts on the chart, you will need to play around with the Data labels.

3. With the visual selected, navigate towards the Format your visual section in the Visualization pane.
4. Click on the Data labels radio button to ON them for this visual. The option is present inside the Visual tab.
5. I have changed the Position to “inside center” to display the labels at the heart of this chart.
6. I increased the Font size to 15 and made it Bold through the Values section.

Using the Card and Visual Level Filter

My go-to method in Power BI to show the count of how many times a value appears inside Power BI involves using the Card visual. I kind of like them because they are simple and on point yet give the most critical information in a first view.

As I said, it is a designated visual to show single-value representations for the different metrics. Let’s see how it can be utilized to show the count of value occurrence.

1. To add the Card visual, go to the Visualizations pane on the right side and click on the Card visual.
2. Expand the Product Data table from the Data pane, drag the Product_Category field, and drop it to the Fields section of the Card visual.

3. Now click on the dropdown next to the dragged column from the Fields section and select the Count to give a count of product categories.

However, the count you see is 500, which is the total count of all categories. But you wanted to check the count for a specific category value. What can you potentially do to show the count of a specific category? Let’s see!

4. Expand the Filters pane beside the Visualization pane and drag the Product_Category column from the table to the Filters on this visual section.

5. From the applied visual level filter, select any specific category (in this case, Accessories) to get the count of occurrence of that specific value on the Card visual.

And Bingo! You get the occurrence count of a specific value inside the Card visual.

Pro Tip: If you see, inside the filter pane itself you can also see the count of occurrence of each category. Which is another way to generate this insight of its own.

Using the Matrix Visual

The Matrix visual in Power BI is a very intuitive cross-tabular visual that works on similar lines to the Pivot Table (part of almost every data tool available). It allows you to aggregate the data based on two categories simultaneously placed in rows and columns. This makes it a perfect choice for showing how many times a value appears in Power BI.

1. Inside Power BI, Go to the Visualizations pane placed on the right-hand side and click on the Matrix visual to add it to the canvas.

You will see three parameter areas to configure this visual: Rows, Columns, and Values.

2. Drag the Product_Category column from the Product Data table to the Rows section.
3. Drag the same column again to the Values section. Click on the dropdown next to it in the Values section and select Count from the entire list of available aggregators.

Perfecto! On the report canvas, you can see a matrix visual representing each value’s occurrence count for the product category.

The Count aggregation is the key here. Once you click on it, it shows the occurrences of each product category as a count of values. There are other aggregators as well.

Note: This method is really great for those who want to see the count of value occurrence for all of the values in your column and not only a specific value. This way, they don’t have to filter out a specific value from the visual level filter. They can, but they don’t need to.

Pro Tip: You can use the same aggregation method in the above example and other visuals to give the same result for the count of value appearance in a given table. Just scratch your head a bit and develop some innovative ideas to get this done visually. Let me know your ideas through the comment section of this post.

Using different DAX Functions and Expressions

In the final section of this article, you will walk through different methods that allow you to count how many times a value appears in Power BI using different combinations of DAX functions and expressions.

You will create a new calculated column and then represent it on the report canvas to show the count of a specific category. You will create measures that will not be stored anywhere in your data model and, by doing that, save a lot of space. Finally, you will create some tables that will give you a filtered overview of the original table with a count of value occurrences.

To go first with this section, let’s create a new table that holds all of your calculated measures. This is an organized way because with it, all of your calculated measures are stored in a single place, and you don’t need to search for them inside your data table.

1. Go to the Modelling tab and click the New table option under the Calculations group. This will allow you to create a new table based on an expression.

2. Inside the Formula bar that appears below, rename the “Table” with “Key_Measures” and click on the Commit button on the left.

Since you want to use this table as a container for all of your measures, there is no need to write any DAX expression. Just giving an intuitive name is enough.

You will see the newly created Key_Measures table inside the Data section. And now you are ready to go! Excited? 😉🤩

Using the DAX COUNTX with Others

The DAX COUNTX is an iterative function (imagine a for loop iterating over each row) that allows you to count the number of non-empty rows from your table that meet a specified condition. The result of this function is a single-value representation. Let’s explore different combinations of this function to generate the count of occurrences of a specific value in your table.

Following is the syntax for the DAX COUNTX Function –

COUNTX(<table>,<expression>)

Where,

• <table> – represents a source table for which the rows are to be counted.
• <expression> – generally, a DAX expression which, after evaluation, generates a set of values for which count is to be given.

Using the DAX COUNTX and Filtered table

In this section, I will introduce you to a combination of the COUNTX and FILTER DAX functions that allows you to count how many times a product category named “Clothing” appears in Power BI.

Let’s first review the syntax of the FILTER function –

FILTER(<table>,<filter>)

Where,

• <table> – is the source table that needs to be sliced.
• <filter> – specifies a conditional expression that returns a Boolean value for each row of the table. Helps in slicing the table.

Inside the Data section, go to the Key_Measures table and right-click on it. The second option reads as the New measure. Click on it to add one to this table.

Type in the following DAX expression inside the formula bar and click the Commit button.

countxndfilter = COUNTX(FILTER('Product Data', 'Product Data'[Product_Category] = "Clothing"), 1)

What happens under the hood from left to right is –

• The measure is renamed “countxndfilter.” This is just a way of giving a name that explains what you are doing inside the measure.
• The COUNTX function is a for loop-like iterative counter function. What it counts is dependent on the conditions inside the parentheses.
• The FILTER function works here as a separate pair of glasses. The function works by looking at all the rows from your Product Data table and only returning those where the Product Category is “Clothing.”
• The COUNTX function then asked to count all the rows where this filtered data view is accurate. Since the output of this FILTER function is a boolean value, and COUNTX needs a number to count the rows, the 1 is relevant there. To quantify the rows.

Now, you understand the logic and use of this measure. But how to show it’s value? 🤔 Think about it; I am sure you already know how to do it! 😁

1. You can use the visuals to show this measure on the report canvas. Let’s go to our buddy named Card visual from the Visualization section and drag it on the report canvas.

2. Drag the countxndfilter measure inside the Fields, and you are through!

You can see that the Card visual shows number 149 representing the count of occurrence of the Product_Category “Clothing” inside Power BI.

Using DAX COUNTX and CALCULATETABLE Functions

You will use the CALCULATETABLE function combined with the COUNTX in this example. The function changes the context a filter is being used on the entire table.

It is often used within DAX expressions where the same aggregation is supposed to be applied to the entire table. For example, in this case, we want to show the count of all the rows from the Product Data table where the Product_Category is “Clothing.”

The syntax for the CALCULATETABLE function is –

CALCULATETABLE(<expression>[, <filter1> [, <filter2> [, …]]])

Where,

• <expression> – specify a formula or table expression for which you want to modify the context.
• <filter1>, <filter2>, ... – represents the filter context defined for the expression from the first parameter. These filter contexts are totally optional, and it’s OK if you don’t specify them in the first place.

To create a measure that returns the count of the appearance of a value in Power BI –

Inside the Key_Measures tablet, create a New measure by right-clicking on the table> write down the following DAX expression inside the Formula bar > click on the Commit button to add a new measure, which calculates how many times the product category Clothing appears inside the Product Data table.

CountCalculateTable = COUNTX(CALCULATETABLE('Product Data', 'Product Data'[Product_Category] = "Clothing"), 1)

Once done, drag the Card visual from the Visualizations pane and drop the CountCalculateTable measure inside the Fields section to see how many times value Clothing appears in your table.

The working of this DAX expression to provide you the specified result is –

• The measure is named as CountCalculateTable.
• The CALCULATETABLE function filters the data for only those rows where the Product_Category is Clothing. It is a temporary filter applied to the original Product Data table.
• The COUNTX function then works as a counter that counts the filtered rows generated by the CALCULATETABLE.

Note: The phrase changing filter context has appeared multiple times in this section, and it simply means that the CALCULATETABLE function temporarily filters the data in the context of the outer function COUNTX. Which makes your measure faster when the reload happens.

Pro Tip: The CALCULATETABLE function is just an extension of the CALCULATE function, which does the same task but on the scaler values rather than the entire table.

Using COUNTAX with Others

The COUNTAX function is similar to the COUNTX but with only one difference: it is more suitable for the columns within a table than the entire table. COUNTX, on the other hand, is versatile and more generalized than COUNTAX in that it can handle any expression with conditions based on a column or the entire table.

Since, in this scenario, you are working more on filtering a specific column (Product_Data), the function can generate the count of how many times a value appears in Power BI.

Everything for this function is the same as COUNTX (Yes! Even explanations are the same). Apart from the context, it can be used.

I am not going to explain this function more for the purpose. But will provide you with two different expressions that can do the work for you using the COUNTAX Function.

Using the DAX COUNTAX and FILTER Function

To return the count of how many times product category Clothing appears inside the table, you can use the following combination of COUNTAX and FILTER functions together to form a measure.

countaxndfilter = COUNTAX(FILTER('Product Data', 'Product Data'[Product_Category] = "Clothing"),1)

Using the DAX COUNTAX and CALCULATETABLE Function

The following combination of COUNTAX and CALCULATETABLE does the same task.

CountaxCalculateTable = COUNTAX(CALCULATETABLE('Product Data', 'Product Data'[Product_Category] = "Clothing"), 1)

Using the COUNTROWS with Others

In this section, let’s work with the COUNTROWS function. It is a simple function that counts how many rows are in your table.

However, it is very rarely used to serve its original purpose. Since it is so powerful if combined with the other expressions, it is often used to return the count of rows based on the filter applied or the context of the table.

This is precisely what you will do with this function by combining it with its different counterparts to generate a count of occurrences of a specific value inside the table.

The syntax for the COUNTROWS function is as below:

COUNTROWS([<table>])

Where,

<table> – represents an optional argument specifying the table name on which the function is applied. It can either be a table name or an expression that returns a table.

Using the DAX COUNTROWS and FILTER Function

In this section, let’s move towards our friend FILTER function, which creates a table based on the conditions, and then that filtered table can be used inside the COUNTROWS function to generate the count of value occurrences.

Use the following DAX expression to create a measure that generates a count of rows from the Product Data table where Product_Category is “Clothing.”

You then can visualize this count through a Card visual.

CountrowsAndFilter = COUNTROWS(FILTER('Product Data', 'Product Data'[Product_Category] = "Clothing"))

Using COUNTROWS and CALCULATETABLE Functions

Here, for this demo, you will use the CALCULATETABLE, which, as you know, temporarily filters the data based on the conditions within the context of the COUNTROWS function to return the count of value appearances.

Following is the DAX expression for this demo –

CountrowsCalculateTable = COUNTROWS(CALCULATETABLE('Product Data', 'Product Data'[Product_Category] = "Clothing"))

Warning: Even though the FILTER and CALCULATETABLE functions look like they are doing similar tasks, don’t ever fall for that!

The FILTER function in the above example creates a separate table in the first demo that satisfies the specific criteria. The CALCULATETABLE creates a temporary table that represents the rows satisfying a specific filter context, and the table is created nowhere.

Using DAX CALCULATE with Others

The DAX CALCULATE function is similar to the CALCULATETABLE function that you studied in the previous section. The function is used to change or modify the filter context of a measure or calculation. The function is generally used 70 to 80% of the time in Power BI for performing various tasks.

The syntax for the CALCULATE function is as below:

CALCULATE(<expression>[, <filter1> [, <filter2> [, ...]]])

Where,

• <expression> – specifies the calculation or the measure you want to modify the context for.
• <filter1>, <filter2>, ... – represents the expressions used to filter the data or to define a new filter context for the expression. These are all optional.

Let’s use it in combination of different other DAX functions.

Using the DAX CALCULATE and COUNTROWS Function

As you are aware, the COUNTROWS returns a count of rows inside a specified table. What if we use it with the CALCULATE and then change the filter context to return only the specified row count? It should work, right?

It indeed works! Let’s see it through an example. Create a new measure using the following expression inside the Key_Measures table.

CalculateAndCountrows = CALCULATE(COUNTROWS('Product Data'), 'Product Data'[Product_Category] = "Clothing")

Here,

• The COUNTORWS function first returns the total count of rows inside the table Product Data which is 500.
• The CALCULATE function then applies a filter context to this data, saying that out of these 500 rows, only show those where Product_Category is “Clothing.” This filter context then returns the count of 149 rows as an output.

If you imagine the previous cases of the COUNTROWS function, inside it, there were expressions slicing the data first and then allowing it to count the filtered rows. Here in this example, it counts everything and then returns count based on the filtering context provided by the CALCULATE function.

There are various ways of applying the filtering context in this expression and a few of them are as below:

ClothingCountValuesContains = CALCULATE(COUNTROWS('Product Data'), CONTAINSSTRING('Product Data'[Product_Category], "Clothing"))

• Here, the CONTAINSSTRING function checks whether the Product_Category column contains the string “Clothing” or not and, based on that, returns a Boolean output, either TRUE or FALSE, based on whether the condition is met or not.
• The COUNTROWS then count all such rows, and returns count for them.

ClothingCountFilterValues = CALCULATE(COUNTROWS('Product Data'), FILTER('Product Data', 'Product Data'[Product_Category] = "Clothing"))

Here, the FILTER function filters all the rows with “Clothing” as a Product_Category to return the count of those.

ClothingCountFilterAll = CALCULATE(COUNTROWS('Product Data'), FILTER(ALL('Product Data'[Product_Category]), 'Product Data'[Product_Category] = "Clothing"))

This approach is pretty useful when you have a predefined filter applied while cleaning the data inside the Power Query Editor.

Suppose you have filtered out a few rows from the Product Data table while transforming the data. Now, you want the count of the rows falling under that category. But you can’t because, from your data model, those rows are filtered out. What will you do?

You can use the above expression where the other part, though, is self-explanatory; the piece of expression to pay attention to is the one encapsulated inside the ALL function.

The ALL Function removes any predefined filters from a specified column and returns full data for the calculation happening in any expression.

I encourage you to use these measures on your own and try to understand how they work. That’s a nice bit of homework for you! 😉

Using the Custom Calculated Tables

Until now, you were creating the measures that were giving you a single value representation of the count of occurrence of a specified value in a table. Through this section, you will learn how to create a custom column that represents the count of value occurrence for each category. These tables are like subsets of the original table.

Try to recall the examples where we created temp tables inside expressions to serve the purpose. This section uses something similar to logic but creates hardcore tables rather than temporary ones.

Using Table Function SUMMARIZE with COUNTROWS

The SUMMARIZE is a table manipulation function. It returns a summarized view of your long data by grouping rows based on a specific column.

For example, in the Product Data table, it can group data by the Product_Category column. Inside the summary, you can use any aggregator that returns MIN, MAX, AVG, or SUM. But in this case, you deliberately will use the COUNTROWS to return the count of values.

The syntax for the SUMMARIZE function is as below:

SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]...[, <name>, <expression>]...)

Here,

• <table> – represents the table you want to group by
• <groupBy_columnName> – Specify the columns (mostly categorical, but not limited to) based on which you want to group the data.
• <name> – is the name of the new column summarized based on the aggregators.
• <expression> – a DAX expression that defines the named column. Returns a scalar value for each row of the <groupBy_columnName>.

Go to the Modeling tab and click on the New table option to create a new table based on the DAX expression.

Use the following DAX expression to create a SummaryTable that returns the count of each Product_Category against a new column “Count.”

SummaryTable = 
SUMMARIZE(
    'Product Data',
    'Product Data'[Product_Category],
    "Count", COUNTROWS('Product Data')
)

Explanation

• The first part of the SUMMARIZE function specifies the table you wish to group by (Product Data).
• Then you need the column based on which the table should be grouped by (Product_Category).
• Finally, you create a new calculated column named “Count” within the SUMMARIZE function itself using the COUNTROWS function, which returns the count of values for each grouped row.

If you visualize this table using the Table or the Matrix visual, it will look like above. Where against each product category the count of value occurrence is shown.

Using Table Functions ADDCOLUMNS, GROUPBY, and Others

Similar to the SUMMARIZE table function, we can use the ADDCOLUMNS, GROUPBY, and a few other functions together to create a summary table with Product_Categories and their counts in Power BI.

Create a New table using the following DAX expression.

CountByValue = 
ADDCOLUMNS(
    GROUPBY('Product Data', 'Product Data'[Product_Category]),
    "Count", COUNTROWS(FILTER('Product Data', 'Product Data'[Product_Category] = EARLIER('Product Data'[Product_Category])))
)

Explanation

• The GROUPBY function allows you to group the data by Product_Category column from the Product Data table. It also creates a table based on these groups.
• The ADDCOLUMNS function adds a new column named “Count” inside this grouped table.
• The COUNTROWS function then used to count how many rows are falling under each product category.
• The FILTER function then creates a temporary table where only those rows are processed where the product category is exactly the same as the current category processed. For each row evaluation, the EARLIER function refers to the current category being checked with the grouped category.

If you visualize it using the Table or Matrix visual, it will look like above.

Conclusion

Phew!😅 That was a Herculean Task to create a comprehensive reference guide about all the methods helping you to count how many times a specific value appears in Power BI. I know the article is quite long, but its intention was to show you all possible ways to achieve this result.

I hope you like these cool (I don’t know how many of those are) ways to count how many times a value appears in Power BI.

Did you like the ways presented in this article? Do you know any other ways to get this task done? Let me know through the comments section (It’s all yours). Until the next time you see me, Ciao! 🙋‍♂️