COUNT , COUNTAX and COUNTX function in DAX Power bi

In DAX, COUNT, COUNTAX, and COUNTX are functions used to count rows or evaluate expressions across rows in a table. They differ in their behavior, input types, and use cases.

---

1. COUNT Function

• Description: Counts the number of non-blank numeric values in a column.
• Syntax:

  COUNT(<column>)

• Behavior:
• Only considers numeric or date/time values.
• Ignores blanks and non-numeric values.

---

Example Using Sample Superstore

Assume the Sales table has the following data:

Order ID	Sales	Region	Discount
O001	100	East	0.10
O002	200	West	0.20
O003	(blank)	North	(blank)
O004	400	South	0.15

Measure: Count the number of sales (non-blank numeric values).

Count Sales = 
COUNT('Sales'[Sales])

• Result: 3 (ignores the blank in the Sales column).

---

2. COUNTAX Function

• Description: Counts rows in a table after evaluating an expression for each row, considering logical and non-numeric values as well.
• Syntax:

  COUNTAX(<table>, <expression>)

• Behavior:
• Evaluates the expression row by row.
• Counts non-blank results of the expression.

---

Example Using Sample Superstore

Task: Count rows where the discount is greater than 0.

Count Discounts = 
COUNTAX('Sales', 'Sales'[Discount] > 0)

• For each row:
• Row 1: 0.10 > 0 → True.
• Row 2: 0.20 > 0 → True.
• Row 3: Blank → False.
• Row 4: 0.15 > 0 → True.
• Result: 3 (True rows).

---

3. COUNTX Function

• Description: Similar to COUNTAX, but focuses on counting numeric results from an expression.
• Syntax:

  COUNTX(<table>, <expression>)

• Behavior:
• Evaluates the expression row by row.
• Counts non-blank numeric results.

---

Example Using Sample Superstore

Task: Count rows where the total sales (calculated as Sales * (1 - Discount)) is non-blank.

Count Adjusted Sales = 
COUNTX('Sales', 'Sales'[Sales] * (1 - 'Sales'[Discount]))

• For each row:
• Row 1: 100 * (1 - 0.10) → 90 (valid numeric value).
• Row 2: 200 * (1 - 0.20) → 160 (valid numeric value).
• Row 3: Blank → Ignored.
• Row 4: 400 * (1 - 0.15) → 340 (valid numeric value).
• Result: 3 (numeric rows).

---

Comparison Table

Function	Counts	Evaluates Expression	Includes Non-Numeric Values	Example Use Case
COUNT	Non-blank numeric values	No	No	Count numeric values in a column (Sales).
COUNTAX	Rows with non-blank results	Yes	Yes	Count rows where a logical condition is met.
COUNTX	Rows with non-blank numerics	Yes	No	Count rows with valid numeric calculations.

---

Practical Application in Power BI Superstore

COUNT Use Case

Total Orders = 
COUNT('Sales'[Order ID])

Counts all non-blank Order IDs.

COUNTAX Use Case

Discounted Orders = 
COUNTAX('Sales', 'Sales'[Discount] > 0)

Counts orders with a non-zero discount.

COUNTX Use Case

Count Profitable Sales = 
COUNTX('Sales', 'Sales'[Sales] - 'Sales'[Discount])

Counts rows with a calculated profit where the result is non-blank.

---

These functions are versatile tools for counting based on specific criteria, offering flexibility depending on your dataset and reporting needs.

The COUNTX function in DAX counts rows after evaluating an expression for each row in a specified table. It is typically used when you want to conditionally count rows based on a calculation or expression.

---

Example with the Sample Superstore Dataset

Scenario: Count Orders with Profitable Sales

We want to count the number of orders where the profit (calculated as Sales - Discount - Cost) is greater than 0.

---

Data Assumptions

Assume the Orders table has the following columns:

• Order ID
• Sales
• Discount
• Cost

Sample Data:

Order ID	Sales	Discount	Cost
1	100	10	80
2	200	20	150
3	50	5	60
4	300	0	200

---

DAX Measure

We calculate a Count of Profitable Orders using COUNTX:

Count of Profitable Orders = 
COUNTX(
    FILTER(
        'Orders',
        ('Orders'[Sales] - 'Orders'[Discount] - 'Orders'[Cost]) > 0
    ),
    'Orders'[Order ID]
)

---

Explanation

Filtered Table:

1. FILTER:

Order ID	Profit Calculation
1	100 – 10 – 80 = 10
2	200 – 20 – 150 = 30
4	300 – 0 – 200 = 100

2. COUNTX:

• It iterates over the filtered table and counts the rows. Here, the expression Order ID is used as the column to count.
• Result: 3 orders (Order IDs: 1, 2, and 4).

---

Output

The measure returns 3, as only 3 orders have positive profit.

---

Additional Examples of COUNTX

1. Count Orders with High Discounts:

   High Discount Orders = 
   COUNTX(
       FILTER('Orders', 'Orders'[Discount] > 15),
       'Orders'[Order ID]
   )

• Counts rows where the Discount is greater than 15.

1. Count Orders with Specific Sales Range:

   Medium Sales Orders = 
   COUNTX(
       FILTER('Orders', 'Orders'[Sales] >= 100 && 'Orders'[Sales] <= 300),
       'Orders'[Order ID]
   )

• Counts rows where Sales fall between 100 and 300.

---

COUNTX is particularly useful for conditional row counts where the logic involves calculations.