Power BI COUNTIF Measure [With Examples]

Are you looking to work with Power BI COUNTIF Measure? If your requirement is yes, check COUNTA() and COUNTROWS() in Power BI DAX.

I recently worked with a Power BI COUNTIF Measure where the requirement was to count the number of rows in a table. For that, I researched some solutions and implemented them in this article.

This Power BI tutorial will discuss what Power BI measures COUNTIF and how to apply the Power BI COUNTIF Measure. Also, we will discuss the COUNTIFBLANK measure in Power BI.

Power BI COUNTIF Measure

Power BI COUNTIF measure is a way to count the number of rows in a table that meets specific conditions. As we know, the COUNTIF() is unavailable in Power BI.

To do this in Power BI, we have two other functions:

  • COUNTA function: This function counts the number of values in the column but doesn’t count the null or blank values.

Power BI COUNTA() syntax:

COUNTA(<Column>)

Where:

Column = The column name that contains the values to be counted.

  • COUNTROWS function: This function counts the number of rows in the table.

Power BI COUNTROWS() syntax:

COUNTROWS([<Table>])

Where:

Table = The table’s name that contains the rows to be counted.

How to Apply Power BI COUNTIF Measure

Now we see how to apply the Power BI COUNTIF function in simple steps:

Here we have a SharePoint list (Financial Sample) that we use in this example:

ColumnsData Types
SegmentSingle line of text
CounrtySingle line of text
ProductSingle line of text
SalesCurrency
ProfitCurrency
Units SoldNumber
How to Use Power BI COUNTIF Function

You may want to count how many times the Canada country appears in the Country column from the above SharePoint list. To do this, follow the below steps:

1. Open Power BI Desktop and load the data. Then, you see data in the Data pane.

COUNTIF Function in Power BI

2. Under the Home tab, click on the New measure.

COUNTIF in Power BI

3. In the formula bar, click the below expression. Then click Commit.

Count Country = COUNTA('Financial Sample'[Country])

Where:

  • Count Country = Name of the measure
  • Financial Sample = Name of the Table
  • Country = Name of the Column
CountA Functions in Power BI

4. Under the Home tab, expand Visual gallery(black box) -> Click the Table visual.

Power BI COUNTIF measure

5. Then, using the +Add data option, add Country, Count Country(Measure)  into Columns field in the table visual.

How to apply COUNTIF logic in Microsoft Power BI

6. Now, Canada’s country appears 19 times in the Country column.

Power Bi CountA function example

Now, we see the same example using the COUNTROWS function in Power BI.

1. Under the Home tab, click on the New measure.

COUNTIF in Power BI

2. In the formula bar, click the below expression. Then click Commit.

Count Table Rows = COUNTROWS('Financial Sample')

Where:

  • Count Table Rows = Name of the measure
  • Financial Sample = Name of the Table
How to use Countif in Power BI

3. Add the Count Table Rows(Measure) in the above table visual.

how to use CountRows Functions in Power BI

4. Now, you see the same result in this example.

CountRows Functions example in Power BI

This way, you can use the COUNTIF Measure in Power BI.

Power BI Measure COUNTIFBLANK

Now we see the countif blank measure and how to use it in Power BI.

In Power BI, count the blank space in the data set. We have a function called COUNTBLANK. This function will count the number of null/ blank values in the column.

Power BI COUNTBLANK function syntax:

COUNTBLANK(<column>)

Where:

Column = The column name that contains the values to be counted.

We have a table that contains Name, Region, and Sales columns. Check the Screenshot below.

COUNTBLANK measure in Power BI

I want to use the COUNTBLANK function to count how many blank spaces are in the Sales column. To do this, follow the below steps:

1. Create the above table in the Power BI and load the data. Then, you see data in the Data pane.

Power bi measure count if blank

2. Under the Home tab, click on the New measure.

COUNTIF blank in Power BI

3. In the formula bar, click the below expression. Then click Commit.

Count Blank = COUNTBLANK('Sales Table'[Sales])

Where:

  • Count Blank = Name of the measure
  • Sales Table = Name of the Table
  • Sales = Name of the Column
COUNTBLANK function In power BI

4. Under the Home tab, expand Visual gallery(black box) -> Click the Card visual

Power BI Count if Blank measure

5. Then, using the +Add data option, add Count Blank(Measure)  into  Field in the Card visual.

COUNTBLANK function example in Power Bi

6. The Card visual shows the number of blank values in the Sales column.

How to Count Blank Values in a Power BI Table

With the help of the COUNTBLANK(), you can count how many blank values are in the Power BI column.

Conclusion

Power BI COUNTIF() measure is a way to count the number of rows in a table that meets specific conditions.

I hope this tutorial will help you to understand Power BI COUNTIF Measure. In this tutorial, we discussed Power BI measures countif in Power BI and how to apply Power BI COUNTIF Measure. Also, we discussed the COUNTIFBLANK measure in Power BI.

Moreover, you may like some more Power BI tutorials: