How to apply COUNTIF logic in Microsoft Power BI
Your email has been sent
There is no COUNTIF function in Microsoft Power BI. If you need to apply that logic, you’ll have to turn to other measures.
Most Microsoft Excel users are familiar with Microsoft Excel’s COUNTIF() function, which allows you to count items conditionally. For instance, you might want a count of employees who joined the organization within the last year, or you might want a list of all students who aren’t passing your class.
If you’ve added Microsoft Power BI to your tool chest, you might be wondering how to do the same thing with a measure. In this tutorial, I’ll show you how to use two Power BI’s counting measures to apply the COUNTIF logic.
SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)
I’m using Power BI on a Windows 10 64-bit system. You can download the demonstration .pbix file, AdventureWork Sales from Github. Once downloaded, double-click the .pbix file to open it in Power BI and follow along.
The technical answer to the question “What is COUNTIF in Power BI?” is nothing. Power BI doesn’t have a COUNTIF measure. Power BI measures are calculations, similar to Microsoft Excel functions.
Excel’s COUNTIF() function is a logical function that counts values in a range, based on a specific condition. In Power BI, you can apply the same logic, but you’re on your own because there is no COUNTIF measure.
The good news is you can do it. The bad news is that you’ll work harder to apply the COUNTIF logic in Power BI than you do in Excel.
When applying COUNTIF logic, you will use two Power BI measures to create a model or explicit measure:
Model measures are measures that you create — they are explicit. Now that you know what you don’t have, let’s take a crack at applying COUNTIF logic in Power BI using COUNTA and COUNTROWS.
Working with the AdventureWorks database, let’s suppose we want a count of the number of customers in a specific ZIP Code area. This will be easy because we’ll work with only one table. Often, you will work with two or even more tables.
In Power BI Desktop, click the Data icon in the left pane and then expand the Customer table in the Fields pane. With the Customer table open, we’re ready to build a new measure so click New Measure in the Calculations group.
Overwrite the default measure name with the following expression, as shown in Figure A:Customer Measure = COUNTROWS(Customer)
Figure A
This measure returns the number of rows in the Customer table. Now let’s use COUNTA to return the number of values in the Postal Code table. To do so, add a second measure using the expressionPostal Measure = COUNTA(Customer[Postal Code])
as shown in Figure B. This measure returns the number of Postal Code values in the Customer table. It doesn’t return distinct values, just values. If you’re thinking that the two measures should return the same number because we’re working with only one table, you’re right.
Figure B
Both measures show up in the Fields pane under the Customer table node. You can access them anytime to modify or delete.
Now it’s time to apply the two measures. To do so, click the Report icon. If there’s a date table displayed, delete it.
In the Visualizations pane, click Matrix because a table view is easier to follow. To fill the matrix visualization, drag the Postal Code field from the Fields pane to the Rows list under the Visualizations options. Next, drag the Customer Measure and the Postal Measure to the Values list.
Figure C shows the resulting matrix visualization. In this case the number of customer rows is the same as the number of postal code values. The truth is, we don’t even need the customer measure. You might want to click Focus Mode to enlarge the values, as I have.
Figure C
We know there’s 18,485 postal code values, but that’s not a COUNTIF logic. It’s a simple count of the values in the Postal Code column. The matrix displays the name of each measure. When creating the measures, keep that in mind so the name of the measure is meaningful as a header.
The resulting table shown in Figure C is an application of the COUNTIF logic because Postal Measure returns a conditional count for each postal code.
There’s more though. Drag Customer from the Fields pane and drop it below the Postal Code field in the Rows list. Figure D shows the results. Each postal code now has a plus sign to the left. Click one to display all the customers in that postal code. If published, this ability will be available to end users.
Figure D
Many end users will rely heavily on counting measures to expose gains, losses, trends and more. Even though Power BI doesn’t offer a COUNTIF() measure, you can apply the same logic using the COUNTA and COUNTROWS measures in Power BI.
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
How to apply COUNTIF logic in Microsoft Power BI
Your email has been sent
TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.
This is a comprehensive list of the best AI art generators. Explore the advanced technology that transforms imagination into stunning artworks.
Find the perfect payroll service for your business without breaking the bank. Discover the top cheap payroll services, features, pricing and pros and cons.
Is NordVPN worth it? How much does it cost and is it safe to use? Read our NordVPN review to learn about pricing, features, security, and more.
Free project management software provides flexibility for managing projects without paying a cent. Check out our list of the top free project management tools.
Australian and New Zealand enterprises in the public cloud are facing pressure to optimize cloud strategies due to a growth in usage and expected future demand, including for artificial intelligence use cases.
The end of a year provides an ideal opportunity for revisiting the previous year’s goals and performance, reviewing personal and professional aspirations and prioritizing the upcoming year’s initiatives. But where to start? Tech pros can implement these seven planning steps from TechRepublic Premium to successfully set and track goals and subsequently prioritize initiatives. From the …
The role of a technical copywriter is recognized as a cornerstone in defining and conveying a company’s identity in the ever-evolving landscape of today’s business, where the online presence and narrative of a brand have outstanding value. This hiring kit from TechRepublic Premium provides an adjustable framework your business can use to find the right …
With artificial intelligence being more popular than free alcohol at a tech conference, it’s wise to stay informed about all things AI or even implement policies for its correct usage. This TechRepublic Premium pack provides readers with seven downloads for a bargain price. The bundle comprises two glossaries about AI and machine learning; three features …
A successful chief digital officer drives their organization’s digital transformation and creates value while rationalizing business processes and the customer experience. This hiring kit from TechRepublic Premium provides a workable framework you can use to find the best CDO for your organization. From the hiring kit: EDUCATION AND EXPERIENCE Candidates must have a degree in …
Get the web’s best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let’s start with the basics.
* – indicates required fields
Lost your password? Request a new password
Please enter your email adress. You will receive an email message with instructions on how to reset your password.
Check your email for a password reset link. If you didn’t receive an email don’t forgot to check your spam folder, otherwise contact support.
This will help us provide you with customized content.
Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add [email protected] to your contacts list.
Leave a Reply