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 COUNTIF function because there isn’t any straight forward 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.
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 unique country list.
- Upload the above two tables to Power BI.
- We have two tables uploaded “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 COUNTA function.
- For COUNTA function Column Name we need to choose the “Country” column from “Data Table”.
COUNTA function job is done here, close the bracket and for the next argument of CALCULATE function Filter1, Open FILTER function.
- The first argument of 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 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 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 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
