COUNTIF is such a commonly used function in Excel that everyone who makes a transition to using Power BI often stumbles on this question – “How can I do a COUNTIF in Power BI ?”

Power BI is very unlike Excel, you don’t have cells where you can type the COUNTIF formula, rather almost everything in Power BI works on filter contexts. In this post I’ll be showing you different ways of writing COUNTIF in Power BI using DAX.

Let’s begin people!

In case you are more of a video person and want to grab it before this long piece of post, here it is. Enjoy!

COUNTIF using Visual / Pivot

Consider this simple “Sales” data with 4 columns. Pretty self-explanatory!

CountIF in Power BI

My Question: Count the number of transactions done in each channel.

Possible Answer: This is easy to solve in a Pivot Table, so is it in Power BI. In Power BI,

  • Create a Matrix Visual (which is the Pivot Table for Power BI)
  • Drag Channel from Sales Table in Rows
  • Drag any other column (let’s say Date Field) in Values, change the calculation to COUNT & rename the field and you are done with your COUNTIF

CountIF in Power BI

This is pretty standard stuff, everyone knowing Pivot would know of it. Before we move on, let me tell you that dragging a column the values section of the pivot table is “looked down” upon in Power BI.. Simple reason – columns are expensive! It’s a good habit to explicitly create measures for even the simplest calculation. Now let’s move ahead..

 

COUNTIF using a Measure

Like a said, measures are a good habit. Let’s create two measures that will work the same

Transaction Count Measure 1 = COUNTA(Sales[Date])

Transaction Count Measure 2 = COUNTROWS(Sales)

In this scenario both the measures will give the same result. Then how are they different?

  1. COUNTROWS function simply counts the number of rows in the table
  2. COUNTA function counts the number of values in the column. The null values (blanks) in the column aren’t counted.
  3. In this example since none of the values in the Date column are blank so both the measures will deliver the same results

Personally I’d recommend Measure 2 over Measure 1

 

COUNTIF in the Fact Table

Consider the same Sales table once again.

CountIF in Power BI

My Question : For some reason you want to add a column in this table to find the frequency of dates. In other words, how many times does each date appear? Again a COUNTIF!

Unfortunately you can’t write =COUNTIF in Power BI, because it doesn’t exist. But that doesn’t mean that you can’t do it. We are going to write a short DAX formula for achieving this. I create a new column in Sales Table with the following code

COUNTIF for Duplicate Dates =
VAR __CurrentRowID = Sales[Date]
RETURN
COUNTROWS(
    FILTER(
        ALL(Sales),
        __CurrentRowID = Sales[Date]
    )
)

See the results.. that’s what we wanted!

CountIF in Power BI

Let me explain

  1. The code begins with a Variable that captures the current row date.
  2. Then in the FILTER function I expand the full Sales table using ALL and see how many dates are matching with the variable (current row date)
  3. Finally wrapping that FILTER in COUNTROWS, I am able to take a count of rows that were matching the current row date
  4. COUTNIF done!

Yeah, I know it’s not that friendly but that’s DAX for you!

if you don’t like using Variables (although you should, but that’s okay!), you can write the same COUNTIF differently.

COUNTIF for Duplicate Dates with Variable =
COUNTROWS(
    FILTER(
        ALL(Sales),
        EARLIER(Sales[Date]) = Sales[Date]
    )
)

The above code will return the same result

An additional layer of complexity could be if you would like to do a COUNTIF but with 2 conditions, that’s what we call a COUNTIFin Excel

Another Question: How many rows have the same Product ID and Channel?

We apply the same logic as above the only difference being that we add 2 conditions using the && operator, take a look

COUNTIF for Same Prod & Channel =
COUNTROWS(
    FILTER(
        ALL(Sales),
        Sales[Product ID]=EARLIER(Sales[Product ID]) &&
        Sales[Channel] = EARLIER(Sales[Channel])
    )
)

The same code could be written using variables as well

COUNTIF for Same Prod & Channel using Variables =
VAR __CurrProd = Sales[Product ID]
VAR __CurrChannel = Sales[Channel]
RETURN
COUNTROWS(
    FILTER(
        ALL(Sales),
        Sales[Product ID]=__CurrProd &&
        Sales[Channel] = __CurrChannel
    )
)

Here are the results

CountIF in Power BI

 

COUNTIF in the Lookup Table

Another thing that a lot of people try to do is try writing a COUNTIF in Lookup Table. Let’s say we have another table, Products, linked to our Sales Table using the common column as Product ID / Code

CountIF in Power BI

Here is how the relationship is set up

CountIF in Power BI

My yet another Question: Create a column in the Products Table to find the number of products sold in the Sales Table?

Since we have a relationship between both tables, this be easily solved using RELATEDTABLE Function

A Quick Note: RELATEDTABLE function is mostly used in Lookup Table (in our case Products) as compared to RELATED function which is mostly used in Fact Table (in our case Sales)

Here is the DAX Code

COUNTIF for Prod Sold in Sales Table =
COUNTROWS(RELATEDTABLE(Sales))
  1. The RELATEDTABLE function returns a Table with matching rows (as per the relationship) from the Sales table
  2. I wrap that in the COUNTROWS function to take its count.
  3. Again COUNTIF done!

CountIF in Power BI

 

Another tricky question could be to Create a column (in Products table) to find the matching Product IDs that sold under Affiliate Channel? This is our classic COUNTIFS yet again but this time in the Lookup Table

Not to worry, all I do is add a condition along with our existing RELATEDTABLE function

COUNTIF for Prod Sold only for Affiliate =
COUNTROWS(
    FILTER(
        RELATEDTABLE(Sales),
        Sales[Channel] = "Affiliate"
    )
)
  1. The FITLER function filters out only those rows which belong to “Affiliate” Channel
  2. COUNTROWS function take the count of those rows
  3. COUNTIF done!

Results..

CountIF in Power BI

Is there any other type of COUNTIF that you are trying to do which I have not spoken about? Please let me know in the comments. I’ll be glad to help

 

Recommendations

Ideally speaking you should be creating measures rather than creating columns in your tables. But for some reason if you do want to create columns for testing or some odd calculations, I hope this helped

Cheers!

– – – – –

 

Update on 13 Dec 2019

Running COUNTIF like Excel using DAX

Another common problem is getting running count in DAX, in simple terms how many times has the current item appeared until the current row. You’d do something like this in Excel =COUNTIF(A$1:A1,A1)

Let’s see how can we do this in Power BI, assume this data!

CountIF in Power BI
yeah.. superheros!

To be able to do this we’ll need an Index column (serial numbers). Easy enough

  1. In the Query Editor >> Add Columns Tab
  2. Index Column >> Starting with 0

the result looks like this!

CountIF in Power BI

Here comes a DAX code for a Calculated Column

Running Count = 
COUNTROWS(
    FILTER(
        ALL(Data),
        Data[Index] <= EARLIER(Data[Index]) &&
        Data[Heros] = EARLIER(Data[Heros])
    )
)

CountIF in Power BI

Take a look at Superman or Spiderman (or any other man) the count is 1 at the first instance and increments each time the “man” re-appears!

Explaining the Formula

  1. ALL function removes the filter from Data table
  2. Then checking for 2 conditions
    • Index should be lesser or equal to the current row Index number AND
    • Hero should be equal to current row Hero
  3. Wrap this in COUNTROWS and this gives us the count till the current row

Done!

 

DOWNLOAD THE POWER BI WORKING FILE

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI