How to apply COUNTIF logic in Microsoft Power BI


There is no COUNTIF function in Microsoft Power BI. If you need to apply that logic, you’ll have to turn to other measures.

Laptop computer displaying logo of Microsoft Power BI
Image: monticellllo/Adobe Stock

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.

What is COUNTIF in Power BI?

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:

  • COUNTA: This measure counts the number of values in a column, but it doesn’t evaluate null or blank values.
  • COUNTROWS: This measure counts the number of rows in the column.

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.

How to apply COUNTIF logic in Power BI

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

Add the first measure.

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 expression

Postal 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

Add the second measure.

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

The matrix visualization returns a list of postal codes and the number of customers in each one.

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

Add customers to the visualization.

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.



Source link