Power BI COUNTIF

COUNTIF Function in Power BI

“COUNTIF” is a logical function to count the values in the range based on the conditions given. As a newcomer, you may not get the logic of using the COUNTIF function because there isn’t any 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 COUNTIF function logic in Power BI even though there isn’t 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)

For all those who have used COUNTIF in excel, they may found it easy because there is a built-in function with excel, but with Power BI, there isn’t any built-in function. This is where the challenge lays for users because to get one job done; users need to understand at least 3 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 appearing in the country column. In this case, our criteria are “Canada” country like this based on different criteria we can count values.

How to Apply the COUNTIF Function in Power BI?

As we have told, there is no built-in COUNTIF function in Power BI. We need to apply a few other functions to get the job done.

Below is the data we are using to apply the COUNTIF Function in Power BI. You can download the workbook from the below link 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 COUNTIF Function.

  1. Upload the above two tables to Power BI.

    Power bi countif (Data Table)

  2. We have two tables uploaded “Data TableData TableA data table in excel is a type of what-if analysis tool that allows you to compare variables and see how they impact the result and overall data. It can be found under the data tab in the what-if analysis section.read more” 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 COUNTA function.

    Power bi countif (CountA)

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

    Power bi countif (Data Table - country)

  7. COUNTA functionCOUNTA FunctionThe COUNTA function is an inbuilt statistical excel function that counts non-blank cells (not empty) in a cell range or the cell reference. For example, cells A1 and A3 contain values but cell A2 is empty. Therefore, the formula “=COUNTA(A1, A2, A3)” returns 2.read more job is done here, close the bracket and for the next argument of CALCULATE function Filter1, Open FILTER function.

    Power bi countif (Filter)

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

    Power bi countif (Filter - data table)

  9. Filter Expression is nothing but for which column of the “Data Table” you want to apply a filter, so it will be “Country Column” from “Data Table.”



    Power bi countif (Country)

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

    Power bi countif (List- Country)

  11. Ok, we are done with formulas. Close two brackets and hit 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 kind of 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 COUNT ROWS 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 that we need to count is “Data Table” so choose the same.
Count rows - Data table
  • For Filter Expression, follow the method that we did in the previous example.
Power bi countif (Count of Countries total)

There you go; we have got the count of each country from the “Data Table.” Like this using a combination of DAX formulas in Power BI, we can replicate the functionality of the COUNTIF of Excel in Power BI as well.

Note: Power BI COUNTIF file can also be downloaded from the link below, and the final output can be viewed.

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.
  • FILTER will apply the filter for the mentioned column with the mentioned criteria.
  • After a filter is applied, COUTNROWS count the number of rows available for the filter.

This has been a guide to Power BI COUNTIF. Here we discuss how to replicate 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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>