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.