Power bi countif and all count functions in dax

Power bi countif is not available in dax functions, but it is very intuitive to implement in DAX. To achieve countif in Power BI, we will use the COUNTROWS function. The syntax of the COUNTROWS function is 

COUNTROWS(<table>).

It takes one parameter as a table and returns the count of all the rows in the provided table.

For the demonstration of power bi countif, I will take the Product and sales table. The product table has one to many relationship with the sales table as you can see below.

Problem statement

We have a lot of sales transactions for different products color. We need to count the number of transactions for blue color products for every related category.

Let’s write one dax formula to count the number of all sales transactions without taking care of colors for every category.

Sales transactions = COUNTROWS(Sales)

 Now we need to plot one visual on the report and drag the category field from the product table and “Sales transactions” measure.

In this figure, we can see that every category has its total sales transaction, but we need sales transactions for only red products. For this, we need to modify the dax formula slightly.

Blue transactions = COUNTROWS(FILTER(Sales,Sales[Product Color] = "Blue"))

This formula works the same as sumif power bi that we had already discussed in another blog post. 

COUNTROWS needs a table to count all the rows in it. Instead of passing a full table, we will give a filtered table that only has blue product transactions.

The filter function FILTER(Sales,Sales[Product Color] = “Blue”) will do this for us.

Below you can see categories with total sales transactions and blue transactions.

Countif power bi with Calculate

Calculate in power bi is a compelling function that performs multiple useful tasks. Countif in power bi can be achieved with the help of Calculate.

Let’s write one formula for countif in dax.

countif w calculate = CALCULATE(COUNTROWS(Sales),Sales[Product Color] = "Blue")

It will provide us the same result as we got by using the Filter function. Calculate takes a minimum of two parameters. The first parameter is the expression, and the second parameter is a filter.

We have provided the first parameter COUNTROWS(Sales) as an expression, and the second parameter is Sales[Product Color] = “Blue” as a filter.

Let’s go further and discuss different flavors of count function in dax.

I have prepared a simple table for the demonstration, as you can see below.

This table has four columns. The first two columns contain text data with empty values. The third column has numeric values with null values, and the fourth column contains logical values with values.

Power BI Count with empty, null and logical values

Count function in power bi counts all the values in the given column. It takes one parameter as a column name.

Let’s write a straightforward formula to count values in the product column.

count_product = COUNT(tblCount[Product])

As we can see, there are eight rows in the table, but the product column has only seven text values and one empty value.

The result of this formula will be eight because it considers empty values during the calculation.

The same result we will get if we apply this formula on product color column as 

Count_color = COUNT(tblCount[Color])

The product color column contains four text values and four empty values. It will give us the same result as we got above for the product column.

Now we will try to count values in the cost column. Let’s write one formula for computing values in the cost column.

Count_cost = COUNT(tblCount[Cost])

The answer this time will be six because there six numeric values, and two values are null. Count in Power BI ignores null values during the calculation as you can see in the above figure.

Let’s apply this formula to our last column named “Conditional” that contains logical values.

This time we will get an error.

Error Message:

MdxScript(Model) (71, 36) Calculation error in measure ‘KPI2′[Count_logical]: The function COUNT cannot work with values of type Boolean.

The error is self-explanatory. It states that count function in dax cannot work on logical values.

That’s all about Power BI Count function.

Now let’s move to the third flavor of power bi count function.

Counta Power BI with logical values

We have seen that count function cannot count a column that has logical values. To solve this problem, we have another useful function in DAX called counta.

Count and counta functions are similar in functionality, but the only difference is that counta function can count logical values in the column.

Counta = COUNTA(tblCount[Conditional])

The result of this function will be five because in “Conditional.”

Column, there are five logical values and three null values. It counts logical values and ignores null values as the “count” function did.

Power BI Countblank to count null and blanks values

Countblank function in power is also a fascinating and compelling function that deals with blank and null values in the column.

This function is a count number of null and blank values in the column. It also counts null values in a column that contains logical values.

The syntax of the countblank function is similar to other flavors of count functions. It takes merely one parameter as a column.

countblank = COUNTBLANK(tblCount[Conditional])

The output of this function will be three because, in the “Conditional” column, we have five logical values and three null values.

In the same way, you can use this function with other columns that contain text values, numeric values with null and blanks.

Power BI countx as a countif in dax

Countx in power bi works the same as we have seen sumx for the implementation sumif. Countx is an iterative function, and it takes two parameters. The first parameter is a table, and the second parameter is an expression.

Let’s create a situation where countx is useful. We need to count all the products for category Bikes. For this, we have to pass the first parameter as a product table to countx function.

countif with countx = COUNTX(FILTER(Products,Products[Category] = "Bikes"),Products[ProductKey])

Instead of providing a complete product table to countx function, we have applied filter function on the product table to get only those rows where the product category is a bike. 

Now we have one new table where the category is a bike for all the available rows. We need to decide now for which column we have to perform counting values. According to the formula, we have chosen Products[ProductKey] column.

The result of this formula will be the count of all the non-empty values in the Products[ProductKey] column.

Power BI countax with filter function

Countax in power bi works the same as counta function in dax. The only difference is that countax is an iterative function that iterates through the provided table using the row context.

For the demonstration purpose, I took the example of my tblProduct table, as you can see below.

Let’s write one formula, and then I will explain in detail.

countax_logical = COUNTAX(FILTER(tblCount,tblCount[Product] = "P1"),tblCount[Conditional])

The countax function takes two parameters, the first one is the table, and the second one is an expression. I need to calculate logical values in the “Conditional” column for all “P1” products.

The first filter table will execute and return four rows where the Product is equal to “P1”. Now we have a table with four rows. After this, we are passing the “Conditional” column as an expression for which we need to count values. 

The answer will be three because we will have one empty value and three non-empty value for the “Conditional” column.

Power Bi distinctcount function

Power BI distinctcount function works the same as we have in SQL. It takes one column as a parameter and counts all the unique values, including empty and null values.

Let’s write one dax formula.

distinctCount values = DISTINCTCOUNT(tblCount[Product])

The result of this dax expression will be four because we have three products P1, P2, P3, and one empty value.

The distinctcount function in dax works on almost every type of values in a column like text, numeric and logical values, etc.

Power BI DISTINCTCOUNTNOBLANK function

According to the Microsoft documentation about the DISTINCTCO

UNTNOBLANK function is “Unlike DISTINCTCOUNT function, DISTINCTCOUNTNOBLANK does not include the BLANK value.” But this information is a bit confusing. 

Let’s look write an expression to elaborate distinctcountnoblank function in dax.

distinctcountNoBlank = DISTINCTCOUNTNOBLANK(tblCount[Product])

The result of this formula will be four. As we have three distinct products and one empty value. But this function is counting empty value as well. Why is that?

Let’s apply this formula on a numeric column and see

distinctcountNoBlank_cost = DISTINCTCOUNTNOBLANK(tblCount[Cost])

The answer to this formula is five, which is correct because we have five unique numeric values and two null values. 

So the conclusion is when we use this formula for text column, it counts blank as well, but when we apply it on numeric or logical values, it does not count null values.

Note: Whenever we enter empty values in the text columns, it behaves as empty values, but when we enter an empty value in numeric or logical columns, it behaves as null values.

The distinctcountnoblank function in dax count unique empty values, but It does not count null values.

You can also learn about if statement in power bi

For more useful blogs, please visit Learn DAX