Tutorials › Power BI › Basic DAX Functions

Basic DAX Functions

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?

Related Topics

DAX IntroductionWhat DAX is, how it works, and why it is the most important Power BI skill to masterMeasures vs Calculated ColumnsWhen to use a DAX measure vs a calculated column — a decision that affects performanceCALCULATE — The Most Important DAX FunctionMaster CALCULATE to control filter context and build any business metric in Power BI