Countif function in Power BI Measure + 10 Useful Examples

In this Power bi tutorial, we will discuss Power bi measure countif with examples in Power bi. And also discuss the below points:

Countif power bi measure
Power bi count if meaure greater than
Power bi measure count if contains text
Power bi measure count if equals
Power bi measure count if not null
Power bi measure count if blank
Power bi measure count if true
Power bi measure count if less than
Power bi measure count if between two dates
Power bi measure count distinct if

Table of Contents

Countif Function in Power BI

Countif function is used in excel to count the values in the range based on the condition given. As we know the countif function is not available in power bi.

So, in Power bi we can apply the same logic of countif function by using the two functions:

CountA function: This function will count the number of values in the column but it doesn’t count the null/ blank value
CountRows function: This funtion simply count the number of rows in the column.

Countif Power bi measure

Here we will discuss the countif measure in Power bi with example. You can download the sample table from here.

Here we will count the number of sales based on the segment using the measure.

Open the Power bi desktop and then Load the data using get data.
We will see the two measure that will work same (will give same output).
One measure we will create using COUNTA function and another measure COUNTROWS function.
Click on the new measure from the ribbon in power bi desktop.
Then write the below measure:

Sales count measure1 = COUNTA(Orders[Sales])

Select the New measure from the ribbon to write the measure using COUNTROWS function. The measure looks like below:

Sales count measure 2 = COUNTROWS(Orders)

Now to see the output, select the matrix visual from the visualization pane.
In the rows field, drag and drop the segment field from the field pane.
In the value field, drag and drop the Sales count measure1 and Sales count measure 2 from the field pane.
Both the measure will give the same result, COUNTA function counts the number of values in the column, and also the null values/blank in column are not counted.
Whereas in COUNTROWS function simply counts the number of rows in the table.

Read Power BI Measure SUM

Power bi count if measure greater than

Here we will see how to count sales that are greater than 0 using the countif function in power bi measure.

We will use the below sample table to count the sales if the sales are greater than 0.

For example, the product name AA = count of sales =2 (greater than 0), similar for BB = 2 and CC = 2.

Load the data using get data.
Then click on the new measure to count the sales which is greater than 0.
Then write the below measure:

Count = Calculate ( Count ('Table'[Sales]), 'Table'[Sales] > 0)

Now to check the measure, click on the table visual from the visualization pane.
In the value field, click on the Product name column and count measure from the field pane.

Read Power bi measure by category

Power bi measure count if contains text

Here we will see how to count the number of rows if it contains a specific text using the Countif function in the measure in power bi desktop.

You can download the sample data from here.

For example: From the subcategory column, we want to count the rows that contain “Art” using the measure.

Load the data using get data.
Click on the New measure from the ribbon in Power bi desktop.
Then write the below measure:

CountArt = CALCULATE(COUNTROWS(Orders), Orders[Sub-Category] = "Art")

To check the measure, select the card visual from the visualization pane.
Then in the Fields add the CountArt measure from the field pane.

Read Power bi measure s ubtract + 7 useful examples

Power bi measure count if equals

Here we will see how to count the values if it equals to the values using the measure in power bi desktop.

We will use the below sample table to count the product order status which can be either delay or on time.

We will count the total numbers of orders delayed using the measure in power bi desktop.

Load the data using get data in power bi desktop.
Click on the new measure from the ribbon.
Then write the below measure:

Measure = CALCULATE(
	COUNT('Table'[Product Id]),FILTER('Table','Table'[Delay Status]="On Time" || 'Table'[Delay Status]="Delay"))

Now to check the measure, select the table visual from the visualization pane.
In the value field, drag and drop the product id column, delay status column, and Measure from the field pane.

Read Power BI Measure IF with Examples

Power bi measure count if not null

Here we will see how to count the not null rows by ignoring the null rows in the power bi desktop.

We will use the below sample table to count the rows if it is/ not null.

Load the data using get data.
Now we will create a measure which will count the rows is not null by ignoring the null or blank rows.
Click on the New measure from the ribbon.
Then write the below measure:

Measure 2 = COUNTA('Table 3'[Last Login])

Now to check the measure, click on the card visual from the visualization pane.
In the Fields, drag and drop the Measure 2 from the field pane.

Read Power BI Date Function with Examples

Power bi measure count if blank

Here we will see how to count if blank using the measure in power bi desktop.

We will use the below sample table to count the blank rows from the column.

Load the data using get data
To count the null or blank rows in the column, we will create a measure.
Select the new measure from the ribbon.
Then write the below measure :

Count blank = COUNTBLANK('Table 3'[Last Login])

Now to check the measure, select the card visual from the visualization pane.
In the fields, drag and drop the Count blank measure from the field pane.

Read Power bi show items with no data

Power bi measure count if true

Here we will see how to count value if it is true using the measure in power bi.

We will use the below sample table which stores boolean values, so here we will count the values if it is true From the element A column.

Load the data using get data.
Then select the new measure from the ribbon to count the value is true from the Element a column.
Then write the below measure:

CountTrue = COUNTAX(FILTER('Table 4',[Element A]=TRUE()),TRUE())

Now to check the measure, select the card visual from the visualization pane.
Then in the fields, drag and drop the CountTrue measure from the field pane.

Read Power bi show value as percentage

Power bi measure count if less than

Here we will see how to count the value less than 250 using the measure in power bi desktop.

We will use the below sample table to count the value which is less than 250.

To count the value which is less than 250 we will create a measure.
Click on the new measure from the ribbon in power bi desktop.
Then write the below measure:

Countless than = Calculate ( Count ('Table'[Sales]), 'Table'[Sales] <250)

Now to check the measure, select the table visual from the visualization pane.
In the value field, drag and drop the product name,and count less than measure.

Read How to create a Power BI Dashboard in Microsoft teams

Power bi measure count if between two dates

Here we will see how to count distinct members for two dates using the measure in power bi desktop.

We will use the below sample table to count the members between two dates.

power bi measure count if between two dates

For example, it will give output for everyday distinct member count.

For Date: 11/06/2018 count =2 (a,b)
Date: 12/06/2018 count =2(a,b).
Date: 13/06/2018 count =2(a,b)
Date 14/06/2018 count =1 (a)
Date:15/06/2018 count =2( a,c), similarly it will show upto 19/06/2018.

Sor for this first we will create a calculated date table. Then we will create a measure that will give the above output.

Load the data using get data.
Click on the Modelling tab -> New table.
Then write the below dax formula to create a table.

Calendar = ADDCOLUMNS(CALENDARAUTO(), "MonthNum", MONTH([Date]), "WeekNum", WEEKNUM([Date], 2))

Now we will create a measure which will count the membership between two dates.
Click on the New measure from the ribbon in power bi desktop.
Then write the below measure:

Measure = CALCULATE(DISTINCTCOUNT('Members'[user_id]), FILTER('Members', 'Members'[membership_period_start]<=min('Calendar'[Date])&&'Members'[membership_period_end]>=min('Calendar'[Date])))

Now to check the measure, click on the table visual from the visualisation pane.
In the value field drag and drop Date column( Calendar table) and Measure (Members table) from the field pane.

Read Power bi sum group by multiple columns

Power bi measure count distinct if

Here we will see how to count the distant number of active members using the measure in Power BI.

We will use the below sample data, in this data set there is the publisher with revenue by month.

So, in the above table publisher are considered to be active months if the revenue is greater than 1000. Now we will count the number of active publishers using the measure.

Load the data using get data.
To create the new measure, click on the New measure from the ribbon.
Then write the below measure:

DistinctCountActiveMonths = 
CALCULATE(
    DISTINCTCOUNT( 'Table2'[Publisher name] )
    ,'Table2'[Active month]= 1
)

Now to check the measure, click on the table visual from the visualization pane.
In the value field, drag and drop the publisher name column and Distinct count active months measure from the field pane.

You may also like the following Power BI tutorials:

In this Power bi tutorial, we learned countif power bi measure in power bi. And also we discuss the below points:

Power bi count if meaure greater than
Power bi measure count if contains text
Power bi measure count if equals
Power bi measure count if not null
Power bi measure count if blank
Power bi measure count if true
Power bi measure count if less than
Power bi measure count if between two dates
Power bi measure count distinct if

Bhawana Rathore

Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com