The COUNTIF is designed to count the number of cells that meet a specific criterion within a range. You can refer to a column, specify a column, and then a criteria to count only those values that match that criterion.
Where is COUNTIF in POWER BI?
Unfortunately, in Power BI, there is no direct COUNTIF function like in Excel, you can use similar functionality using DAX (Data Analysis Expressions), which is Power BI’s formula language designed for creating custom calculations.
You can create a COUNTIF formula in Power BI through the CALCULATE function, combined with COUNTROWS for counting the number of rows that meet a specific condition.
- CALCULATE – CALCULATE (<expression>,<filter1>,<filter2>,…)
- COUNTROWS – COUNTROWS ( <table> )
Apart from this, ahead in the tutorial, we will also learn to write a DAX formula to write a NESTED IF formula in the Power BI.
So let’s get started…
Steps to Write a COUNTIF Formula in Power BI
In our example, we have data with dates and quantities. Now, we need to count the number of rows where the quantity is below 500. For this, let’s follow the below steps:
- Load Data to the Power BI: You first need to load the data to the Power BI. As I said, we have a small data table with dates and quantities.
- Create a New Measure – In Power BI, go to the “Modeling” tab on the top menu. Click on “New Measure.” B button to open the
- Write the DAX Formula: You need to use the CALCULATE function combined with COUNTROWS for counting and filtering the rows based on your criteria within the same function. And the formula that you need to enter is Above500 = CALCULATE(COUNTROWS(Sheet1),Sheet1[Quantity]>=500). And then enter to add the new measure to the data.
- Formula Breakdown: To break it down further:
- Above500 is the name of the new measure that will be created using this formula.
- CALCULATE is a DAX function that evaluates an expression in a context that is modified by the specified filters.
- COUNTROWS is another DAX function that counts the number of rows in the specified table or in a table defined by an expression. Here, it counts the rows in “Sheet1.”
- Sheet1[Quantity]>=500 is the filter condition applied. This condition filters the rows in “Sheet1” and only includes the rows where the value in the “Quantity” column is greater than or equal to 500.
- Your Measure in Visualizations: In the end, click OK to add the measure. Once your measure is created, it will appear in the Fields pane under the table to which it was added. You can now drag this measure into your reports and use it in various visualizations, such as tables, charts, or cards, to display the count based on your criteria.
Nested COUNT IF in Power BI
You can also nest IF statements to evaluate multiple conditions. Let’s say you want to check for the March month in the date column and then quantity below 500:
Above500March = CALCULATE ( COUNTROWS ( Sheet1 ), Sheet1[Quantity] >= 500, Sheet1[Dates] <= DATE ( 2024, 03, 31 ) )
- Above500March is the name of the new measure that will be created using this formula.
- CALCULATE is a DAX function that evaluates an expression in a context that is modified by the specified filters.
- COUNTROWS is another DAX function that counts the number of rows in the specified table or in a table defined by an expression. In this case, it counts the rows in “Sheet1.”
- Sheet1[Quantity]>=500 is the first filter condition applied. This condition filters the rows in “Sheet1” and only includes the rows where the value in the “Quantity” column is greater than or equal to 500.
- Sheet1[Dates]<=DATE(2024,03,31) is the second filter condition applied. This condition filters the rows in “Sheet1” and only includes the rows where the date is on or before March 31, 2024.
The CALCULATE function changes the context of the data only to include rows where the “Quantity” is 500 or more and the date is on or before March 31, 2024.
Then, the COUNTROWS function counts the number of rows in this filtered data. The result is stored in the new measure “Above500March”.
And in the same way, you can also use the combination of CALCULATE, COUNTROWS, and FILTER function to write a nested COUNTIF in Power BI.
Above500March = CALCULATE ( COUNTROWS ( Sheet1 ), FILTER ( FILTER ( Sheet1, Sheet1[quantity] <= 500 ), Sheet1[Dates] <= DATE ( 2024, 03, 31 ) ) )
To understand this formula, you need to split into two parts:
- The first part is COUNTROWS(Sheet1), which counts the number of rows in ‘Sheet1’.
- The second part is a nested FILTER function:
- The inner FILTER function, FILTER(Sheet1,Sheet1[quantity] <= 500), creates a new table that only includes the rows from ‘Sheet1’ where the ‘quantity’ is less than or equal to 500.
- The outer FILTER function further refines this table to include only the rows where the ‘Dates’ are on or before March 31, 2024. The DATE function DATE(2024,03,31) specifies this date.
- The CALCULATE function then applies these filters to the original COUNTROWS function.
Use COUNTIF Measure in Power BI
Now, you can use this measure in any Visual in the Power Bi canvas. For this, you need to go back to the “Report View.”
In the Visualizations pane on the right side of the interface, find the “Card” icon. Click on this icon to create a new card visualization on your report canvas.
With the card visualization selected, go to the Fields pane. Drag the measure or field you want to display on the card.
Here, enter use “Above500march”.
That’s it.