FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

Power BI COUNTIF

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

COUNTIF Function in Power BI

COUNTIF function is a logical function to count the values in the range based on the conditions. As a newcomer, you may not get the logic of using the COUNTIF functionCOUNTIF FunctionThe COUNTIF function in Excel counts the number of cells within a range based on pre-defined criteria. It is used to count cells that include dates, numbers, or text. For example, COUNTIF(A1:A10,”Trump”) will count the number of cells within the range A1:A10 that contain the text “Trump” read more because there is no straightforward COUNTIF function with Power BI. However, still, we can apply the same logical function, which works like the COUNTIF in Power BI. In this article, we will take you through how to replicate the COUNTIF function logic in Power BI even though there is not any COUNTIF function with Power BI.

Power-BI-COUNTIF

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Power BI COUNTIF (wallstreetmojo.com)

All those who have used COUNTIF in Excel may find it easy because there is a built-in function in Excel. But with Power BI, there is not any built-in function. Instead, it is where the challenge lies for users because to get one job done, users need to understand at least three other DAX functions.

For example, look at the below data range in Excel.

Power BI Countif (Excel Data)

From the above list, you may want to count how many times the “Canada” country appears in the “Country” column. In this case, our criteria are “Canada” country like this based on different criteria we can count values.

–>> If you want to learn Power BI professionally, then Power BI Basics Course (5+ hours) is the perfect solution. This course will introduce you to a wide range of Power BI concepts like collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights, Creating Reports in Power BI Desktop, Analyze Data with Excel, Graphs & Visualizations, Interactive Dashboards along with some real life examples.

How to Apply the COUNTIF Function in Power BI?

As we have been told, Power BI has no built-in COUNTIF function. Therefore, we must apply a few other functions to complete the job.

Below is the data we will use to apply the COUNTIF function in Power BI. You can download the workbook from the link below and can use it to practice with us.

You can download this Power BI COUNTIF Excel Template here – Power BI COUNTIF Excel Template
Power BI Countif (Excel tables)

The first table has a country-wise sales value and the second table has a unique country list. So, from the first table, we need to get the count of the unique country list.

Follow the below steps to apply the COUNTIF function.

  1. Upload the above two tables to Power BI.


    Power bi countif (Data Table)

  2. We uploaded two tables, “Data Table” and “List.” Right-click on the “List” table and choose “New column.”


    Power bi countif (new column)

  3. Now, give a name to the new column.


    Power bi countif (column name)

  4. Open the CALCULATE function.


    Power bi countif (calculate)

  5. For Expression, open the COUNTA function.


    Power bi countif (CountA)

  6. For the COUNTA function “ColumnName,” we need to choose the “Country” column from “Data Table.”


    Power bi countif (Data Table - country)

  7. The COUNTA function job is done here. Close the bracket and for the next argument of CALCULATE function Filter1. Open the FILTER function.


    Power bi countif (Filter)

  8. The first argument of the FILTER function is “Table,” which is from which table we need to count the country column, so choose “Data_Table.”


    Power bi countif (Filter - data table)

  9. The FilterExpression is nothing but for which column of the “Data_Table” you want to apply a filter so that it will be “Country Column” from “Data_Table.”


    Power bi countif (Country)

  10. Now, it will apply the filter. Once the filter is applied, we need to count the country based on the unique list in the “List” table, so put an equal sign and choose “Country” from the “List” table.


    Power bi countif (List- Country)

  11. Now, we are done with formulas. Close two brackets and press the “Enter” key to get the result.


    Power bi countif (Country Count)

As you can see, we have got each country count in the “Data Table.” Like this, we can apply a COUNTIF function in Power BI to get the job done.

Alternative Method of COUNTIF Function

We can also apply one more method to get the country count, just like the COUNTIF function, i.e., using the COUNTROWS method.

  • Right-click on choosing the “New column” option.
Power bi countif (new column)
  • This time gives a different name than the previous one.
Power bi countif (Count of Countries)
  • Open COUNTROWS function.
CountRows
  • For this function, open the FILTER function again.
Count rows Filter
  • The Table we need to count is “Data_Table,” so choose the same.
Count rows - Data table
  • For FilterExpression, follow the method that we did in the previous example.
Power bi countif (Count of Countries total)

We got each country’s count from the “Data Table.” Using a combination of DAX formulas in Power BIDAX Formulas In Power BIIn Power Bi, DAX stands for Data Analysis and Expression and is a functional language that represents all of the functions in Excel and Power BI. Formulas in Power BI can be created using the DAX language.read more, we can replicate the functionality of the COUNTIF of Excel in Power BI as well.

Note: We can also download the Power BI COUNTIF file from the link below. We can view the final output.

You can download this Power BI COUNTIF Template here – Power BI COUNTIF Template

Things to Remember

  • There is no built-in COUNTIF function in Power BI.
  • The FILTER will apply the filter for the mentioned column with the mentioned criteria.
  • After a filter is applied, COUNTROWS counts the number of rows available for the filter.

This article has been a guide to Power BI COUNTIF. Here, we discuss how to replicate the COUNTIF logical function in Power BI to count the values in the given range, along with examples. You may learn more about Power BI from the following articles: –