SUM, COUNT, AVERAGE, MIN, MAX, DISTINCTCOUNT — the foundation of DAX
These are the aggregation functions you will use in almost every Power BI report. They are straightforward — but understanding their exact behaviour, especially COUNTROWS vs COUNT vs DISTINCTCOUNT, prevents calculation errors.
Unlike Excel, DAX functions always reference a table column using the format: TableName[ColumnName]. You will never reference a cell range like A1:A100. This explicit referencing is what makes DAX formulas work correctly across all filter contexts.
Examples
Core aggregation functions
SUM — adds all values in a column
Total Sales = SUM(Sales[Amount])
AVERAGE — arithmetic mean
Avg Order Value = AVERAGE(Sales[Amount])
COUNT — counts rows with non-blank numeric values
Count Orders = COUNT(Sales[OrderID])
COUNTA — counts non-blank values (any data type)
Count Products = COUNTA(Sales[ProductName])
COUNTROWS — counts all rows in a table (including blanks)
Total Rows = COUNTROWS(Sales)
DISTINCTCOUNT — counts unique values (very useful!)
Unique Customers = DISTINCTCOUNT(Sales[CustomerID])
Unique Products = DISTINCTCOUNT(Sales[ProductID])
MIN / MAX
Earliest Order = MIN(Sales[OrderDate])
Largest Sale = MAX(Sales[Amount])
SUMX — iterates row by row (covered in Iterators topic)
Revenue = SUMX(Sales, Sales[Qty] * Sales[Price])
💡 DISTINCTCOUNT is extremely useful for business metrics — how many unique customers bought this month? How many unique products were sold?
Difference between COUNT, COUNTA, COUNTROWS
Example table: Sales
OrderID | Amount | Notes
1001 | 45000 | Express
1002 | 18000 | (blank)
1003 | (blank)| Urgent
1004 | 22000 | (blank)
COUNT(Sales[Amount]) = 3 ← skips blank in Amount (row 3)
COUNTA(Sales[Notes]) = 2 ← skips blanks in Notes (rows 2,4)
COUNTROWS(Sales) = 4 ← counts all 4 rows regardless
DISTINCTCOUNT(Sales[Notes]) = 3 ← "Express", "Urgent", blank
blank counts as one distinct value
Key Points
✓Always use TableName[ColumnName] syntax — never reference ranges
✓COUNTROWS counts every row including those with blank values
✓COUNT only counts numeric non-blank values — use COUNTA for text columns
✓DISTINCTCOUNT is one of the most useful business metrics functions
✓MIN/MAX work on dates too — MIN(DateColumn) gives the earliest date
Practice Question
You want to count how many unique customers placed orders. Your Sales table has a CustomerID column where the same customer can appear in multiple rows. Which DAX function should you use?