How to do COUNTIF in Power BI

How to do COUNTIF in Power BI

This is the 2nd post of the Excel-to-Power BI series and in this post, we gonna discuss how to count with condition or perform a COUNTIF( ) in Power BI.

Is there a COUNTIF in Power BI

No. COUNTIF is not directly available to use in Power BI. However we can do count with condition/conditions in Power BI. To do that we need to combine CALCULATE and a few other functions.

This condition/conditions can be involved numbers or text values, and multiple columns across multiple tables in the model. Let's find out how.

COUNTIF with a text condition

We have a products table with Sales Orders, Product & qty.

We need to count all transactions with Corset.

EXCEL

=COUNTIF(SalesQty[Sales],"Corset")

The Equivalent in POWER BI

Create a new measure and add this.

Transactions - Corset =
CALCULATE (
    COUNT ( 'Sales Table'[Sales Order] ),
    'Sales Table'[Product] = "Corset"
)

Add a new measure to the table or other visuals.

COUNTIF with number condition

Let's count sales orders with more than 10 qty.

EXCEL

= COUNTIF(SalesQty[Qty],">10")

The Equivalent in POWER BI

Create a new measure and add this.

Transactions - more than 10 = 
CALCULATE (
    COUNT ( 'Sales Table'[Sales Order] ),
    'Sales Table'[Qty] > 10
)

Add a new measure to the table or other visuals

COUNTIF with multiple conditions

Let's count sales records with Corsets and more than 10 qty.

EXCEL

=COUNTIFS(SalesQty[Product],"Corset", SalesQty[Qty],">10")

The Equivalent in POWER BI

Create a new measure and add this.

Transactions - Corset more than 10 = 
CALCULATE (
    COUNT ( 'Sales Table'[Sales Order] ),
    'Sales Table'[Product] = "Corset",
    'Sales Table'[Qty] > 10
)

Add a new measure to the table or other visuals

Conclusion

COUNTIF or COUNTIFS functions are not available in their original format in Power BI. Same function can be performed on Power BI data by using a combination of CALCULATE and conditional operators.