Table Of Contents
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.
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.
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.
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.
- Upload the above two tables to Power BI.
- We uploaded two tables, "Data Table" and "List." Right-click on the "List" table and choose "New column."
- Now, give a name to the new column.
- Open the CALCULATE function.
- For Expression, open the COUNTA function.
- For the COUNTA function "ColumnName," we need to choose the "Country" column from "Data Table."
- The COUNTA function job is done here. Close the bracket and for the next argument of CALCULATE function Filter1. Open the FILTER function.
- 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."
- 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."
- 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.
- Now, we are done with formulas. Close two brackets and press the "Enter" key to get the result.
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.
- This time gives a different name than the previous one.
- Open COUNTROWS function.
- For this function, open the FILTER function again.
- The Table we need to count is "Data_Table," so choose the same.
- For FilterExpression, follow the method that we did in the previous example.
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.
