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.
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.
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.
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.
- Upload the above two tables to Power BI.
- 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.”
- Now give a name to the new column.
- Open the CALCULATE function.
- For Expression, open COUNTA function.
- For COUNTA function Column Name, we need to choose the “Country” column from “Data Table.”
- 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.
- 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.”
- 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.”
- 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.
- Ok, we are done with formulas. Close two brackets and hit 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 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.
- This time gives a different name than the previous one.
- Open COUNTROWS function.
- For this function, open the FILTER function again.
- The Table that we need to count is “Data Table” so choose the same.
- For Filter Expression, follow the method that we did in the previous example.
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.
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.
Recommended Articles
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
50.3K subscribers