Chapter = 6 Mastering Mathematical Functions in DAX

DAX Functions Explained – Complete Handbook

Aggregation Functions in DAX

Function	Definition	Syntax	Example
SUM	Adds all numbers in a single column.	SUM(<Column>)	Total Sales = SUM(Sales[Amount]) {100,200,300} → 600
SUMX	Calculates expression for each row, then sums.	SUMX(<Table>, <Expression>)	Total Revenue = SUMX(Sales, Sales[Quantity] * Sales[Price])
AVERAGE	Returns mean of numeric column.	AVERAGE(<Column>)	Avg Sale = AVERAGE(Sales[Amount]) {100,200,300} → 200
AVERAGEX	Average from row-by-row expression.	AVERAGEX(<Table>, <Expression>)	Avg Order Value = AVERAGEX(Sales, Sales[Quantity] * Sales[Price])
MIN	Smallest value in a column.	MIN(<Column>)	Lowest Sale = MIN(Sales[Amount])
MINX	Minimum of custom expression.	MINX(<Table>, <Expression>)	Lowest Order Value = MINX(Sales, Sales[Quantity] * Sales[Price])
MAX	Largest value in a column.	MAX(<Column>)	Highest Sale = MAX(Sales[Amount])
MAXX	Maximum of custom expression.	MAXX(<Table>, <Expression>)	Highest Order Value = MAXX(Sales, Sales[Quantity] * Sales[Price])

Logical Functions in DAX

Function	Definition	Syntax	Example
IF	Creates conditional logic.	IF(<LogicalTest>, <True>, <False>)	Profit Status = IF(Sales[Profit] > 0, "Profit", "Loss")
SWITCH	Simplifies multiple IF conditions.	SWITCH(<Expression>, <Value>, <Result>, …)	Grade = SWITCH(TRUE(), Marks>=90,"A", Marks>=75,"B", Marks>=50,"C","Fail")
AND	TRUE if both conditions are true.	AND(<Logical1>, <Logical2>)	HighValueSale = IF(AND(Sales[Amount]>1000, Sales[Quantity]>5),"Yes","No")
OR	TRUE if at least one condition is true.	OR(<Logical1>, <Logical2>)	SpecialOffer = IF(OR(Sales[Discount]>0, Sales[Coupon]=TRUE),"Yes","No")
NOT	Returns opposite of logical test.	NOT(<Logical>)	NoDiscount = IF(NOT(Sales[Discount]>0),"No Discount","Discount Given")

Text Functions in DAX

Function	Definition	Syntax	Example
LEN	Number of characters in text.	LEN(<Text>)	LEN(Customer[Name]) → "Aditya" = 6
LEFT	Extracts characters from left.	LEFT(<Text>, <NumChars>)	LEFT(Customer[Phone],3)
RIGHT	Extracts characters from right.	RIGHT(<Text>, <NumChars>)	RIGHT(Customer[Phone],4)
MID	Extracts substring from position.	MID(<Text>, <Start>, <NumChars>)	MID(Customer[Code],2,3) → "X12345" = "123"
TRIM	Removes extra spaces.	TRIM(<Text>)	TRIM(Customer[Name])
UPPER	Converts to uppercase.	UPPER(<Text>)	UPPER(Customer[Name])
LOWER	Converts to lowercase.	LOWER(<Text>)	LOWER(Customer[Name])
PROPER	Converts to Proper Case.	PROPER(<Text>)	PROPER(Customer[Name]) → "john smith" = "John Smith"