Syntax of COUNTX
The syntax of the COUNTX function is as follows:
The `table` argument is the name of the table or a table expression that you want to count the rows of, and the `expression` argument is the expression that you want to use as the criteria for counting the rows. The `expression` argument can be any valid DAX expression that returns a scalar value.
Using COUNTX to Count Rows
Let's start by looking at a simple example of how to use the COUNTX function. Suppose you have a table named `Sales`, which contains data about the sales of various products. To count the number of rows in the `Sales` table that have a total sales amount greater than $1000, you can use the following DAX formula:
=COUNTX(Sales, IF(Sales[TotalSales]❱1000, 1))
In this formula, we use the IF function to evaluate the condition `Sales[TotalSales]❱1000` for each row in the `Sales` table. If the condition is true, the IF function returns a value of 1, which is then counted by the COUNTX function. Finally, the COUNTX function returns the total count of all rows where the condition is true.
Using COUNTX with Multiple Tables
COUNTX can also be used with multiple tables. Suppose you have two tables named `Sales` and `Products`, which are related by the `ProductID` column. To count the number of products that have been sold at least once, you can use the following DAX formula:
=COUNTX(Products, IF(CALCULATE(SUM(Sales[Quantity]), Sales)❱0, 1))
In this formula, we use the CALCULATE function to evaluate the expression `SUM(Sales[Quantity])`, which calculates the total quantity of products sold across all rows in the `Sales` table. If the total quantity is greater than 0, the IF function returns a value of 1, which is then counted by the COUNTX function. Finally, the COUNTX function returns the total count of all rows in the `Products` table where the condition is true.
Using COUNTX with Filter Expressions
COUNTX can also be used with filter expressions. Suppose you have a table named `Sales`, which contains data about the sales of various products, and you want to count the number of rows where the sales were made in the year 2020. To do this, you can use the following DAX formula:
In this formula, we use the YEAR function to extract the year component from the `Date` column in the `Sales` table. The expression `YEAR(Sales[Date])=2020` evaluates to TRUE for all rows where the sales were made in the year 2020. The COUNTX function then counts the total number of rows where the condition is true and returns the result.
In conclusion, the COUNTX function is a powerful and versatile DAX function that allows you to count the number of rows in a table that satisfy a specified condition. By understanding the syntax and usage of COUNTX, you can create more insightful and accurate measures in your Power BI reports.
Power BI DAX Training Courses by G Com Solutions (0800 998 9248)
Power BI DAX Intensive Training Course£1,050.00 – £26,550.00
Power BI DAX Introduction£395.00 – £9,750.00
Power BI DAX Intermediate£395.00 – £9,750.00
Power BI DAX Advanced£395.00 – £9,750.00