How to use Power BI DAX Math Functions with examples?. Microsoft Power BI DAX provides various Math or mathematical Functions such as CEILING, FLOOR, CURRENCY, INT, SQRT, POWER, MOD, DIVIDE, ROUND, ROUNDUP, ROUNDDOWN, LCM, GCD, etc.
To demonstrate the Power BI DAX Math functions, we are going to use the below shown data. As you can see, there are 15 records in this table.
Power BI DAX Math Functions
The following series of examples show you the use of DAX Math Functions
DAX CEILING Function
The DAX Ceiling function in Power BI returns the closest value, which is greater than or equal to a given value. The syntax of this CEILING Function is:
CEILING(Number, significance)
To demonstrate these DAX Math functions, we have to use the calculated column. In order to create a column, please click on the New Column option under the Home tab or Modeling tab.
As you can see from the screenshot below, we renamed the default column name as CEIL. The below statement finds the closest integer value greater than or equal to the Sales
CEILING(EmployeeSales[Sales])
Let me add this CEILING field to the table that we created earlier. Please refer Create Table Report article to understand the steps involved in creating a table
DAX FLOOR Function
The Power BI DAX FLOOR function returns the closest value, which is less than or equal to a given value. The syntax of this DAX Floor Function is:
FLOOR(expression, significance)
This DAX math function finds the closest value which is less than or equal to Sales
FLOOR(EmployeeSales[Sales], 1)
DAX ROUND Function
The Power BI DAX ROUND function is to round the given values to the closest value. The syntax of this DAX Round Function is:
ROUND(expression, significance)
It rounds the Sales values to the nearest integer
ROUND = ROUND(EmployeeSales[Sales], 0)
Power BI DAX Math CURRENCY Function
The DAX CURRENCY function converts the value into the currency data type. The syntax of this DAX CURRENCY Function is:
CURRENCY(expression)
It converts the Sales values to Currency data type
Money = CURRENCY(EmployeeSales[Sales])
DAX INT Function
The DAX INT function in Power BI converts the given value into an integer data type. The syntax of this DAX INT Function is:
INT(expression)
The DAX INT function converts the Sales values to Integer data type
INT = INT(EmployeeSales[Sales])
Power BI DAX Math SQRT Function
The DAX SQRT function in Power BI returns the square root of a given number. The syntax of the DAX SQRT Function is as shown below:
SQRT(expression)
It returns the square root of a Sales column values
SQRT = SQRT(EmployeeSales[Sales])
DAX SIGN Function
The DAX SIGN function returns the sign of a given number. This function returns 1 for Positive Values, -1 for Negative Values, and 0 for Zeros. The syntax of this DAX SIGN Function is:
SIGN(expression)
It returns the sign of the Service Grade column values
SIGN = SIGN(EmployeeSales[Service Grade])
DAX EVEN Function
The DAX EVEN function returns the nearest even integer of a given number. The syntax of this DAX EVEN Function is:
EVEN(expression)
The below statement returns the nearest even integer of a Service Grade column values
EVEN = EVEN(EmployeeSales[Service Grade])
DAX ODD math Function
The DAX ODD function returns the nearest odd integer of a given number. The syntax of this DAX ODD Function is:
ODD(expression)
It returns the nearest odd integer of a Service Grade column values
EVEN = EVEN(EmployeeSales[Service Grade])
DAX FACT Math Function
The DAX FACT function finds the factorial of a given number. The syntax of this DAX FACT Function is:
FACT(number)
For example factorial of 4 = 4 * 3 * 2 * 1 => 24
The below statement returns the factorial of a yearly income divided by 1000.
FACT = FACT(EmployeeSales[Yearly Income] / 1000)
DAX POWER Function
This DAX function finds the Power of a given number. This function accepts a second argument to specify the raised value. The syntax of this DAX POWER Function is:
POWER(expression, raise_number)
For example Power(4, 3) = 4³.
The below statement calculates and returns the sales to the power of two.
POWER = POWER(EmployeeSales[Sales], 2)
DAX DIVIDE Function
The Power BI DAX DIVIDE function in Power BI divides one value against another. If there is an error or unable to divide, then it returns BLANK values. The syntax of this DAX DIVIDE Function is:
DIVIDE(numerator, denominator)
The below statement divides sales column values by 30
POWER = POWER(EmployeeSales[Sales], 2)
DAX DIVIDE Function also accepts the third argument. Use this argument to specify the alternative number for BLANK values.
The below statement divides sales column values by Grade column values. If the result is blank, then 9 returned.
POWER = POWER(EmployeeSales[Sales], 2)
DAX ABS Function
The Power BI DAX ABS function returns the absolute positive value. The syntax of this DAX ABS Function is:
ABS(expression)
It returns the absolute positive value from the Service Grade column values
ABS = ABS(EmployeeSales[Service Grade])
DAX MOD Function
The DAX MOD function returns the remainder after the number is divided. The syntax of this DAX MOD Function is:
MOD(number, divisor)
For example MOD(5, 2) = 1.
The below statement returns the remainder of Sales divisible by the SQRT function result.
MOD = MOD(EmployeeSales[Sales], EmployeeSales[SQRT])
DAX MROUND Function
The DAX MROUND function returns a number rounded to the specified multiple. The syntax of this DAX MRound Function is:
MROUND(expression, multiple)
The statement that we used is:
MROUND = MROUND(EmployeeSales[Sales], 0.05)
Power BI DAX Math QUOTIENT Function
The DAX QUOTIENT function returns the integer portion of the division result. The syntax of the DAX QUOTIENT Function is:
QUOTIENT(numerator, denominator)
The below statement returns the integer portion of Sales divided by the Mod function result:
QUOTIENT = QUOTIENT(EmployeeSales[Sales], EmployeeSales[MOD])
DAX RAND Function
The Power BI DAX RAND function returns the random positive number between 0 and 1. The syntax of the DAX RAND Function is:
RAND()
Let me create a new column to generate random values
RAND = RAND()
DAX RANDBETWEEN Function
The DAX RANDBETWEEN function in Power BI returns the random number between the user-specified start and end values. The syntax of the DAX RANDBETWEEN Function is:
RANDBETWEEN(start, end)
Let me create a new column to generate random values between 10 and 50
RANDBET = RANDBETWEEN(10, 50)
DAX TRUNC Function
The Power BI DAX TRUNC function truncates the given number to an integer by removing the decimals. The syntax of the DAX TRUNC Function is:
TRUNC(number, num_of_digits)
This DAX math function truncates the Sales column values
TRUNC = TRUNC(EmployeeSales[Sales], 1)
DAX ROUNDDOWN Math Function
The DAX ROUNDDOWN function rounds down the given number towards a zero. The syntax of the DAX ROUNDDOWN Function is:
ROUNDDOWN(number, num_of_digits)
It rounds down the Sales column values to 2 digits.
ROUNDDOWN = ROUNDDOWN(EmployeeSales[Sales], -2)
DAX ROUNDUP Math Function
The DAX ROUNDUP function rounds up the given number away from zero. The syntax of the DAX ROUNDUP Function is:
ROUNDUP(number, num_of_digits)
The below statement rounds up the Sales column values to 2 digits (i.e., greater than the original).
ROUNDUP = ROUNDUP(EmployeeSales[Sales], -2)
DAX LCM Function
The DAX LCM function finds the Least Common or Lowest Common from multiple integer values. The syntax of this Power BI DAX LCM Function is:
LCM(expression1, expression2,.....)
It returns the Least Common from the SQRT result and MOD function result
LCM = LCM(EmployeeSales[SQRT], EmployeeSales[MOD])
Power BI DAX GCD Math Function
The DAX GCD function finds the Greatest Common Divisor from multiple integer values. The DAX GCD Function syntax is:
GCD(expression1, expression2,.....)
This DAX math function returns the Greatest Common Divisor of the SQRT function result and MOD result
GCD = GCD(EmployeeSales[SQRT], EmployeeSales[MOD])
