WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn Power BI » Power BI COUNTIF

Power BI COUNTIF

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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 unique country list.

  • Upload the above two tables to Power BI.

Power bi countif (Data Table)

  • We have two tables uploaded “Data Table” and “List.” Right-click on the “List” table and choose “New Column.”

Power bi countif (new column)

  • Now give a name to the new column.

Power bi countif (column name)

  • Open the CALCULATE function.

Power bi countif (calculate)

  • For Expression, open COUNTA function.

Power bi countif (CountA)

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

Data Table - country

COUNTA function job is done here, close the bracket and for the next argument of CALCULATE function Filter1, Open FILTER function.

Filter

  • 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 - 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.”

Country

  • 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)

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.

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 –

  • Excel COUNTIFS Function
  • COUNTIF Multiple Condition
  • Power BI Average
  • Power BI Matrix
9 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Download Power BI COUNTIF Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Power BI COUNTIF Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More