Power BI COUNTIF

Last Updated :

21 Aug, 2024

Blog Author :

N/A

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya

Table Of Contents

arrow

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 function 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

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.

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.

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 BI, 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.

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: -