There is no COUNTIF function in Microsoft Power BI. If you need to apply that logic, you’ll have to rely on other steps.
Image: monticellllo/Adobe Stock The majority of Microsoft Excel users recognize with Microsoft Excel’s COUNTIF()function, which allows you to count items conditionally. For example, you may want a count of workers who signed up with the company within the last year, or you may want a list of all students who aren’t passing your class. If you’ve added Microsoft Power BI to your tool chest, you may 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 procedures to apply the COUNTIF reasoning. SEE: Google Office vs. Microsoft 365: A side-by-side analysis w/checklist(TechRepublic Premium)I’m using Power
Must-read Windows coverage Excel’s COUNTIF( )function is a rational function that counts values in a range, based upon a particular condition. In Power BI, you can apply the exact same logic, however you’re on your own because there is no COUNTIF step. The good news is you can
do it. The problem is
that you’ll work harder to apply the COUNTIF reasoning in Power BI than you perform in Excel. When applying COUNTIF logic, you will utilize two Power BI determines to produce a model or specific procedure: COUNTA: This step counts the variety of worths in a column
, however it does not examine null or blank worths. COUNTROWS: This step counts the number of rows in the column. Model steps are procedures
that you create– they are specific. Now that you know what you do not have, let’s take a fracture at applying COUNTIF logic
- in Power BI utilizing COUNTA and COUNTROWS. How to apply COUNTIF logic in Power BI Dealing with the AdventureWorks database, let’s suppose we desire a count of the variety of consumers in
a particular postal code area. This will be easy due to the fact that we’ll work with just one table. Typically, you will deal with two and even more tables. In Power BI Desktop, click the Information icon in the left pane and then broaden the
Consumer table in the Fields pane
. With the Client table open, we’re all set to construct a brand-new procedure so click New Measure in the Computations group. Overwrite the default step name with the following expression, as shown in Figure A: Customer Step =COUNTROWS(
Client )Figure A Add the very first step. This procedure returns the variety of rows in the Client table. Now let’s utilize COUNTA to return the variety of values in the Postcode table. To do so, include a second measure using the expression Postal Measure=COUNTA(Customer [Postcode] as shown in Figure B. This measure returns the variety of Postal Code values in the Client table. It does not return unique worths, simply values.
If you’re thinking that the two procedures ought to return the same number because we’re working with just one table, you’re right. Figure B Include the 2nd measure.
Both procedures appear in the Fields pane under the Customer table node. You can access them anytime to customize or erase. Now it’s time to apply the 2 measures. To do so, click the Report icon. If there’s a date table showed, erase it. In the Visualizations pane, click
Matrix because a table view is much easier to follow. To fill the matrix visualization, drag the Postal Code field from the Fields pane to the Rows list under the Visualizations choices.
Next, drag the Consumer Step and the Postal Procedure to the Values list. Figure C shows the resulting matrix visualization
. In this case the number of consumer rows is the very same as the number of postal code values.
The truth is, we don’t even need the customer procedure. You may want to click Focus Mode to enlarge the worths, as I have. Figure C The matrix visualization returns a list of postcodes and the number of customers in every one. We know there’s 18,485 postcode worths, however that’s not a COUNTIF reasoning. It’s an easy count of the values in the Postal Code column. The matrix shows the name of each step. When creating the steps, keep that in mind
so the name of the procedure is meaningful as a header. The resulting table shown in Figure C is an application of the COUNTIF logic since Postal Step returns a conditional count for each postal code. There’s more though. Drag Client from the Fields pane and drop it listed below the Postal Code field in the Rows list. Figure D reveals the outcomes. Each postcode now has a plus sign to the left. Click one to display all the customers because postal code. If published, this capability will be offered to end users. Figure D Add consumers to the visualization.
Many end users will rely greatly on counting procedures to expose gains, losses, trends and more. Although Power BI does not offer a COUNTIF ()procedure, you can use the very same logic using the COUNTA and COUNTROWS steps in Power BI. Source
