Module 5
8 min read
Basic DAX Functions
Learn essential DAX functions for everyday analysis
DAX Function Categories
DAX functions are organized into categories. Here are the essential ones you'll use daily.
Text Functions
| Function | Purpose | Example |
|---|---|---|
| & | Join text | [First] & " " & [Last] |
| LEFT | First N chars | LEFT([Code], 3) |
| RIGHT | Last N chars | RIGHT([Code], 2) |
| UPPER | Uppercase | UPPER([Name]) |
| LOWER | Lowercase | LOWER([Name]) |
| TRIM | Remove spaces | TRIM([Name]) |
| LEN | Text length | LEN([Name]) |
Example - Full Name:
FullName = TRIM([FirstName]) & " " & TRIM([LastName])
Date Functions
| Function | Purpose | Example |
|---|---|---|
| TODAY | Current date | TODAY() |
| NOW | Current datetime | NOW() |
| YEAR | Extract year | YEAR([Date]) |
| MONTH | Extract month | MONTH([Date]) |
| DAY | Extract day | DAY([Date]) |
| QUARTER | Get quarter | QUARTER([Date]) |
| DATEDIFF | Days between | DATEDIFF([Start], [End], DAY) |
Example - Age Calculation:
Age = DATEDIFF([BirthDate], TODAY(), YEAR)
Logical Functions
IF
Status = IF([Amount] >= 1000, "High", "Low")
SWITCH (Better than nested IF)
Tier = SWITCH(
TRUE(),
[Amount] >= 5000, "Gold",
[Amount] >= 1000, "Silver",
"Bronze"
)
AND / OR
Eligible = IF(AND([Age] >= 18, [Score] >= 70), "Yes", "No")
Math Functions
| Function | Purpose | Example |
|---|---|---|
| ROUND | Round number | ROUND([Value], 2) |
| ABS | Absolute value | ABS([Number]) |
| MOD | Remainder | MOD([Num], 3) |
| POWER | Power of | POWER([Num], 2) |
| SQRT | Square root | SQRT([Num]) |
Iterator Functions (X Functions)
These loop through each row and calculate:
| Function | Purpose | Example |
|---|---|---|
| SUMX | Sum of expression | SUMX(Sales, [Qty] * [Price]) |
| AVERAGEX | Average of expression | AVERAGEX(Sales, [Rev] - [Cost]) |
| COUNTX | Count expression | COUNTX(Sales, [Amount]) |
| MAXX | Max of expression | MAXX(Sales, [Rev] - [Cost]) |
| MINX | Min of expression | MINX(Sales, [Rev] - [Cost]) |
When to use X functions:
// When you need row-level calculation first
Total = SUMX(Sales, Sales[Quantity] * Sales[Price])
Filter Functions
FILTER
Big Orders = COUNTROWS(FILTER(Sales, Sales[Amount] >= 1000))
ALL (Remove filters)
% of Total = DIVIDE(
[Total Sales],
CALCULATE([Total Sales], ALL(Sales))
)
RELATED (Get from related table)
Category = RELATED(Products[Category])
Quick Reference Card
| Category | Most Used |
|---|---|
| Aggregation | SUM, AVERAGE, COUNT, DISTINCTCOUNT |
| Text | &, LEFT, UPPER, TRIM |
| Date | YEAR, MONTH, DATEDIFF, TODAY |
| Logic | IF, SWITCH, AND, OR |
| Filter | FILTER, ALL, CALCULATE |
| Iterator | SUMX, AVERAGEX, COUNTX |
| Relationship | RELATED, RELATEDTABLE |
Real-World Examples
1. Customer Full Name:
FullName = UPPER([FirstName]) & " " & PROPER([LastName])
2. Days Since Order:
DaysSince = DATEDIFF([OrderDate], TODAY(), DAY)
3. Profit with Safe Division:
Margin % = DIVIDE([Revenue] - [Cost], [Revenue], 0)
4. Conditional Sum:
Premium Sales = SUMX(
FILTER(Sales, [Amount] >= 1000),
[Amount]
)
Start with SUM, IF, and CALCULATE. Add more functions as you need them. Practice makes perfect!