Basic DAX Functions

What You'll Learn

• Essential DAX functions
• Text, date, and logical functions
• Iterator functions
• Filter functions

Text Functions

CONCATENATE & &

Join text: FullName = [FirstName] & " " & [LastName]

Or: FullName = CONCATENATE([FirstName], " ", [LastName])

LEFT, RIGHT, MID

Extract characters: First3 = LEFT([ProductCode], 3) Last2 = RIGHT([ProductCode], 2) Middle = MID([Text], 2, 3)

UPPER, LOWER, PROPER

Change case: Uppercase = UPPER([Name]) Lowercase = LOWER([Name]) ProperCase = PROPER([Name])

LEN

Text length: NameLength = LEN([CustomerName])

SUBSTITUTE

Replace text: Clean = SUBSTITUTE([Text], "old", "new")

TRIM

Remove extra spaces: Clean = TRIM([Name])

Date & Time Functions

TODAY, NOW

Current date/time: Today = TODAY() CurrentTime = NOW()

YEAR, MONTH, DAY

Extract date parts: OrderYear = YEAR([OrderDate]) OrderMonth = MONTH([OrderDate]) OrderDay = DAY([OrderDate])

QUARTER

Get quarter: QTR = QUARTER([Date])

WEEKDAY, WEEKNUM

Day of week: DayNum = WEEKDAY([Date]) WeekNumber = WEEKNUM([Date])

DATE

Create date: NewDate = DATE(2024, 12, 25)

DATEDIFF

Days between dates: DaysDiff = DATEDIFF([StartDate], [EndDate], DAY)

Parameters:

• DAY, MONTH, QUARTER, YEAR

EOMONTH

End of month: MonthEnd = EOMONTH([Date], 0) NextMonthEnd = EOMONTH([Date], 1)

Logical Functions

IF

Simple condition: Category = IF([Amount] >= 1000, "High", "Low")

Nested: Tier = IF([Amount] >= 5000, "Gold", IF([Amount] >= 1000, "Silver", "Bronze"))

AND, OR, NOT

Multiple conditions: Eligible = IF(AND([Age] >= 18, [Score] >= 70), "Yes", "No")

Premium = IF(OR([Country] = "USA", [Country] = "Canada"), TRUE, FALSE)

NotVIP = IF(NOT([Type] = "VIP"), "Regular", "VIP")

SWITCH

Better than nested IF:

Rating = SWITCH( [Score], 5, "Excellent", 4, "Good", 3, "Average", 2, "Poor", "Unrated" )

With ranges: Tier = SWITCH( TRUE(), [Amount] >= 5000, "Gold", [Amount] >= 1000, "Silver", "Bronze" )

ISBLANK

Check for blank: HasValue = IF(ISBLANK([Field]), "No", "Yes")

Mathematical Functions

ROUND, ROUNDUP, ROUNDDOWN

Rounding: Rounded = ROUND([Value], 2) RoundedUp = ROUNDUP([Value], 0) RoundedDown = ROUNDDOWN([Value], 0)

ABS

Absolute value: AbsValue = ABS([Number])

MOD

Remainder: Remainder = MOD([Number], 3)

SQRT, POWER

Square root: SquareRoot = SQRT([Number])

Power: Squared = POWER([Number], 2)

Iterator Functions

Functions that loop through rows.

SUMX

Sum with row-level calculation: Total = SUMX(Sales, Sales[Quantity] * Sales[Price])

Different from: Total = SUM(Sales[Amount])

AVERAGEX

Average of calculated values: Avg Profit = AVERAGEX(Sales, [Revenue] - [Cost])

COUNTX

Count non-blank calculated values: Count = COUNTX(Sales, [Revenue] - [Cost])

MAXX, MINX

Max/min of calculation: MaxProfit = MAXX(Sales, [Revenue] - [Cost]) MinProfit = MINX(Sales, [Revenue] - [Cost])

Filter Functions

FILTER

Return filtered table: HighSales = FILTER(Sales, Sales[Amount] >= 1000)

Use in measures: Big Orders = COUNTROWS(FILTER(Sales, Sales[Amount] >= 1000))

ALL

Remove all filters: All Sales = CALCULATE([Total Sales], ALL(Sales))

Remove filter from specific column: AllProducts = CALCULATE([Total Sales], ALL(Products[Category]))

ALLEXCEPT

Remove all filters except: Sales Except Date = CALCULATE([Total Sales], ALLEXCEPT(Sales, Sales[Date]))

VALUES

Get list of filtered values: ProductList = VALUES(Products[ProductName])

Count distinct in filter context: Distinct Products = COUNTROWS(VALUES(Products[ProductID]))

DISTINCT

Get distinct values: UniqueProducts = DISTINCT(Products[ProductName])

Similar to VALUES but handles relationships differently

Aggregation Functions

SUM, AVERAGE, MIN, MAX

Already covered, but key functions!

COUNT, COUNTA

COUNT: Counts numbers COUNTA: Counts non-blank

DISTINCTCOUNT

Unique values: Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

Relationship Functions

RELATED

Get value from one side of relationship: Product Category = RELATED(Products[Category])

Must have 1:many relationship!

RELATEDTABLE

Get table from many side: Customer Orders = COUNTROWS(RELATEDTABLE(Orders))

Used in calculated columns on "one" side

Information Functions

ISBLANK

Check if blank: IsEmpty = ISBLANK([Field])

ISNUMBER

Check if number: IsNum = ISNUMBER([Field])

ISTEXT

Check if text: IsTxt = ISTEXT([Field])

Combining Functions

Real-world patterns:

Clean full name: FullName = TRIM(UPPER([FirstName])) & " " & PROPER([LastName])

Age from birthdate: Age = DATEDIFF([BirthDate], TODAY(), YEAR)

Conditional aggregation: Premium Sales = SUMX( FILTER(Sales, RELATED(Products[Category]) = "Premium"), Sales[Amount] )

Safe division with formatting: Margin = VAR Revenue = SUM(Sales[Revenue]) VAR Cost = SUM(Sales[Cost]) RETURN IF(Revenue = 0, BLANK(), DIVIDE(Revenue - Cost, Revenue))

Try This Exercise

Create these:

1. Text manipulation: ProductCode = LEFT([ProductName], 3) & "-" & TEXT([ProductID], "000")

2. Date calculation: DaysSincePurchase = DATEDIFF([OrderDate], TODAY(), DAY)

3. Conditional logic: Status = SWITCH( TRUE(), [Amount] >= 1000, "High", [Amount] >= 500, "Medium", "Low" )

4. Iterator: Total Profit = SUMX(Sales, [Revenue] - [Cost])

5. Filtered count: Premium Count = COUNTROWS(FILTER(Products, Products[Category] = "Premium"))

Next Module Preview

Module 3 covers Visualizations - turning your data and measures into beautiful, interactive charts!

Congratulations!

You now have a solid DAX foundation! Practice these functions and you'll be ready for advanced patterns.

Tip: Learn functions by using them. Start simple, build complexity gradually!