Introduction
This is the third part of the DAX Functions series. You can also visit our other articles in the same series. i.e.
DAX Math and Trig functions
ABS function in DAX
Returns the absolute (positive) value of a number.
Syntax
ABS(number)
Example
ABS(Sales[Profit])
Returns the profit as a positive number regardless of loss/gain.
SQRT function in DAX
Returns the positive square root of a number.
Syntax
SQRT(number)
Example
SQRT(Sales[Area])
Returns the square root of the area.
RAND function in DAX
Returns a random number between 0 and 1.
Syntax
RAND()
Example
RAND() * 100
Returns a random number between 0 and 100.
PI function in DAX
Returns the constant value of Pi.
Syntax
PI()
Example
PI() * Sales[Radius]^2
Returns the circle area using the radius and Pi constant.
COS function in DAX
Returns the cosine of an angle.
Syntax
COS(angle)
Example
COS(45)
Returns 0.707, the cosine of a 45-degree angle.
SIN function in DAX
Returns the sine of an angle.
Syntax
SIN(angle)
Example
SIN(90)
Returns 1, the sine of a 90-degree angle.
DAX Parent and Child functions
PATH function in DAX
Returns the path of column names from a parent to a child column. Useful for hierarchical data.
Syntax
PATH(child_column, parent_column)
Example
PATH(Employees[FullName], Employees[ManagerFullName])
Returns the path of managers from employee to manager.
PATHITEM function in DAX
Returns element at the specified position in a path returned by PATH function.
Syntax
PATHITEM(path, position)
Example
PATHITEM(PATH(Employees[FullName], Employees[ManagerFullName]), 1)
Returns the first element in the manager path for an employee.
PATHLENGTH function in DAX
Returns the number of elements in a path returned by the PATH function.
Syntax
PATHLENGTH(path)
Example
PATHLENGTH(PATH(Employees[FullName], Employees[ManagerFullName] ))
Returns number of managers between employee and the top manager.
PARENT function in DAX
Returns the parent row value from a parent-child hierarchy.
Syntax
PARENT(child_column)
Example
PARENT(Employees[FullName])
Returns manager name for an employee from the hierarchy.
DAX Relationship Functions
RELATED function in DAX
Returns related table row based on join key.
Syntax
RELATED(table, key)
Example
RELATED(Products, Sales[ProductKey])
Returns related Products row for each Sales row based on joined ProductKey.
RELATEDTABLE function in DAX
Returns related table filtered to related rows from the main table.
Syntax
RELATEDTABLE(table, key)
Example
RELATEDTABLE(Products, Sales[ProductKey])
Returns Products table filtered to rows related to the Sales table via ProductKey.
USERELATIONSHIP function in DAX
Enables cross-filtering related tables via relationships without specifying direction.
Syntax
USERELATIONSHIP(table1[key], table2[key])`
Example
USERELATIONSHIP(Sales[CustomerKey], Customers[CustomerKey])
Enables filtering Customers table by selecting Sales rows via relationship.
ISONORAFTER function in DAX
Check if the row date is on or after the filter context date.
Syntax
ISONORAFTER(date, date2)
Example
CALCULATE(SUM(Sales[Revenue]),
ISONORAFTER(Sales[OrderDate],
LASTDATE(Calendar[Date])))
Total revenue for orders on or after the last date in the Calendar table filter context.