A robust set of cloud-based business analytics tools called Power BI makes it simple to combine data from many sources, analyze and visualize information, and share insights. The Mathematical functions in Excel are quite similar to those in Data Analysis Expressions (DAX). Many functions in the Data Analysis Expression (DAX) return tables as opposed to values. The table is used as input for other functions but is not displayed. Trigonometric operations produce a result. However, some of the most popular data analyses use Measures. A measure is added to the field after the query has been executed.
DAX Math Functions
Let’s practice some of the commonly used DAX Math Functions on a sample dataset of library supplies companies. The screenshot of the dataset is given below:
Now, go to the Power BI desktop to load the dataset from excel.
Power BI Desktop
In the image given below, the dataset is uploaded in Power BI.
Load the dataset
Let’s now understand the important DAX math functions in detail:
DAX ABS Math Function
This function returns a number’s absolute value. Whether whole or decimal, a decimal number without its sign represents a number’s absolute value. When nested in functions that demand a positive integer, you can use the ABS function to make the guarantee that only non-negative numbers are returned from expressions.
Syntax: ABS(<number>)
- number: The number you wish to round, or a mention of a column of numbers.
Calculating the difference between the Sum of the Tax amount (INR) and the Sum of the Total amount (INR).
Example: ABS = ABS(572369.5499999996 – 6943454.36)
DAX Ceiling Math Function
This function increases a number by rounding it up to the nearest integer or significant multiple. The two CEILING functions in DAX differ in the following ways:
The Excel CEILING function mimics the behavior of the Excel CEILING function. When determining the ceiling value, the ISO.CEILING function behaves as specified by ISO.
Syntax: CEILING(<number>, <significance>)
- number: The number you wish to round, or a mention of a column of numbers.
- significance: The significant multiple that you want to round. For instance, enter 1 to round to the nearest integer.
Example: ceiling = CEILING(572369.5499999996, 0.01)
DAX Convert Math Function
The convert function performs the transformation of a data type-specific expression.
Syntax: CONVERT(<Expression>, <Datatype>)
- Expression: Any valid expression
- Datatype: A list that comprises the following types of data: INTEGER (Whole Number), DOUBLE (Decimal Number), STRING (Text), BOOLEAN (True/False), CURRENCY (Fixed Decimal Number), and DATETIME (Date, Time, etc.)
Converting a Date datatype to Integer,
Example: convert = CONVERT(DATE(1997,12,23), INTEGER)
DAX Currency Math Function
The currency function returns the result as a currency data type after evaluating the argument.
Syntax: CURRENCY(<value>)
- value: Any DAX expression that only produces a single scalar value and must be evaluated exactly once before any subsequent operations.
Example: currency = CURRENCY(23.44)
DAX Degrees Math Function
The degrees function converts a given angle from radians into degrees.
Syntax: DEGREES (angle)
- angle: The angle that you want to convert, in radians.
Example: degrees = DEGREES(PI())
DAX Divide Math Function
This function divides and provides a different result, or BLANK () when dividing by 0, as appropriate.
Syntax: DIVIDE(<numerator>, <denominator> [, <alternate result>])
- numerator: The division factor or number.
- denominator: The number that must be divided by.
- alternate result: The value returned when a division by zero goes wrong (optional). In the absence of a value, the default is BLANK ().
Example: divide = DIVIDE(234,12)
divide
DAX Even Math Function
The number is given back and rounded to the next even integer. This function can be used to process objects that come in pairs. A packing box, for instance, can accommodate rows of one or two things. When the number of objects, rounded to the nearest two, equals the crate’s capacity, the crate is considered full.
Syntax: EVEN (number)
- number: The number you wish to round.
Example: even = EVEN(23.44)
DAX Exp Math Function
Returns e to the power of the specified number. The natural logarithm’s base, 2.71828182845904, is equal to the constant e.
Syntax: EXP(<number>)
- number: The base e was affected by the exponent. The natural logarithm’s base, 2.71828182845904, is equal to the constant.
Example: exp = EXP(4)
exp
DAX Fact Math Function
The series 1*2*3*…*, ending in the provided number, is returned as the factorial of the supplied number.
Syntax: FACT(<number>)
- number: The positive, non-negative number that you want to compute the factorial for.
Example: fact = FACT(5)
DAX Floor Math Function
Brings a number down to its nearest multiple of importance by rounding it down toward zero.
Syntax: FLOOR(<number>, <significance>)
- number: The number you intend to round.
- significance: The number you wish to round to in multiples. Both the quantity and the significance of the arguments must be either positive or negative.
Example: floor = FLOOR(245.33,0.3)
DAX GCD Math Function
This function returns the largest common divisor of two or more integers. The biggest integer that divides both numbers 1 and 2 without leaving a residual is known as the greatest common divisor.
Syntax: GCD (number1, [number2], …)
- number1: Number 1 is necessary; all other numbers are optional. values from 1 to 255. All values that are not integers are trimmed.
Example: gcd = GCD(122,4)
DAX INT Math Function
Round and gives a number down to the nearest integer.
Syntax: INT(<number>)
- number: The number you intend to round.
Example: int = INT(55.44)
int
DAX Iso.Ceiling Math Function
Increases a number by rounding it up to the nearest integer or significant multiple.
Syntax: ISO.CEILING(<number> [, <significance>])
- number: The number you wish to round, or a mention of a column of numbers.
- significance: The significant multiple you want to round to (optional). For instance, enter 1 to round to the nearest integer. The amount is rounded up to the next integer if the unit of importance is not given.
Example: iso.ceiling = ISO.CEILING(2.999,0.2)
DAX LCM Math Function
The least frequent multiple of integers is returned. The smallest positive integer that is a multiple of all the integer parameters (number1, number2, etc.) is known as the least common multiple. To add fractions with various denominators, use LCM.
Syntax: LCM (number1, [number2], …)
- number1: Number 1 is necessary; all other numbers are optional. For numbers between 1 and 255, you wish to find the least frequent multiple. Value is shortened if it is not an integer.
Example: lcm = LCM(45,56)
DAX LN Math Function
Gives a number’s natural logarithm. Based on the value of the constant e, natural logarithms (2.71828182845904).
Syntax: LN(<number>)
- number: The positive integer whose natural logarithm you seek.
Example: ln = LN(0.004)
ln
DAX LOG Math Function
Gives a number’s logarithm to the base you specify.
Syntax: LOG(<number>, <base>)
- number: The positive number you’re looking for the logarithm for.
- base: The logarithm’s base. When left out, the base is 10.
Example: log = LOG(1035,3)
DAX LOG10 Math Function
Gives the base-10 logarithm of a number.
Syntax: LOG10(<number>)
- number: A positive number for which the base-10 logarithm is desired.
Example: log10 = LOG10(1035)
DAX MOD Math Function
Returns the leftover amount after dividing a number by a divisor. The sign of the result is always the same as the divisor.
Syntax: MOD(<number>, <divisor>)
- number: The figure for which you need to calculate the divisional remainder.
- divisor: The figure you want to divide by.
Example: mod = MOD(1035,5)
DAX MROUND Math Function
Gives you a number with the necessary multiple rounded off.
Syntax: MROUND(<number>, <multiple>)
- number: The number to round.
- multiple: The significant multiple that you wish to round the number to.
Example: mround = MROUND(455.6,5)
DAX ODD Math Function
The number is given back, rounded to the nearest odd integer.
Syntax: ODD (number)
- number: The number to round.
Example: odd = ODD(455.6)
DAX Pi Math Function
Gives Pi in decimal form, 3.14159265358979, with a 15-digit precision.
Syntax: PI()
Example: pi = PI()
DAX Power Math Function
Returns the value of a number raised to a power of exponent.
Syntax: POWER(<number>, <power>)
- number: Any real number can serve as the base number.
- power: The higher exponent from the base number.
Example: power = POWER(5,3)
DAX Quotient Math Function
The division is done, and just the integer part of the result is returned. When you want to throw away the divisional leftover, use this function.
Syntax: QUOTIENT(<numerator>, <denominator>)
- numerator: The division factor, or dividend.
- denominator: The divisor, or quantity by which to divide.
Example: quotient = QUOTIENT(234,3)
DAX Radians Math Function
Converts an angle from degrees to radians.
Syntax: RADIANS (angle)
- angle: An angle that you want to convert from degrees (Required)
Example: radians = RADIANS(90)
DAX Rand Math Function
Returns a uniformly distributed random number that is higher than or equal to 0 and less than 1. Every time the cell containing this function is recalculated, a different number is returned.
Syntax: RAND ()
To avoid mistakes like division by zero, the RAND function cannot return a value of zero.
Example: rand = RAND()
DAX RandBetween Math Function
Gives you a random number that falls between the two values you provide.
Syntax: RANDBETWEEN(<bottom>, <top>)
- bottom: The smallest integer that will be returned by the function.
- top: The function will return the greatest possible integer.
Example: randbetween = RANDBETWEEN(23,44)
DAX Round Math Function
Rounds a value to the specified number of digits.
- The number is rounded to the specified number of decimal places if num digits are higher than zero.
- The number is rounded to the nearest integer if the num digit is 0.
- The number is rounded to the left of the decimal point if the num digits are less than 0.
Syntax: ROUND(<number>, <num_digits>)
- number: The value you intend to round.
- num_digits: The number of digits you wish to round to. A value of zero rounds to the nearest integer; a negative value rounds digits to the left of the decimal point.
Example: round = ROUND(23.44444,4)
DAX RoundDown Math Function
Rounds a number in the direction of zero.
- The value in number is rounded down to the provided number of decimal places if the num digits are more than zero.
- The value in number is rounded to the nearest integer if the num digits are 0.
- The value in number is rounded to the left of the decimal point if the num digits are less than 0.
Syntax: ROUNDDOWN(<number>, <num_digits>)
- number: The real value you intend to be rounded down.
- num_digits: The number of digits you wish to round to. A value of zero rounds to the nearest integer; a negative value rounds digits to the left of the decimal point.
Example: round down = ROUNDDOWN(23.44444,1)
DAX RoundUp Math Function
It rounds a number up, away from 0 (zero).
- The number is rounded up to the provided number of decimal places if the num digits are larger than 0 (zero).
- The number is rounded up to the nearest integer if the num digit is 0.
- The number is rounded to the left of the decimal point if the num digits are less than 0.
Syntax: ROUNDUP(<number>, <num_digits>)
- number: The real value you intend to round up.
- num_digits: The number of digits you wish to round to. If the num digits are 0 or omitted, the number is rounded to the nearest integer when a negative value rounds to the left of the decimal point.
Example: round up = ROUNDUP(23.44444,1)
DAX Sign Math Function
Identifies a number’s sign, a calculation’s outcome, or a value in a column. If the number is positive, the function returns 1, if it is zero, it returns 0, and if it is negative, it returns -1.
Syntax: SIGN(<number>)
- number: A column of numbers, a real number, or a phrase that results in a number.
Example: sign = SIGN(23.44444)
DAX Sqrt Math Function
Gives the square root of a number.
Syntax: SQRT(<number>)
- number: A column of numbers, the number for which you want the square root or a phrase that evaluates to a number.
Example: sqrt = SQRT(23.44444)
DAX SqrtPi Math Function
Gives the (number times pi) square root.
Syntax: SQRTPI (number)
- number: Required, the value that pi is multiplied by.
Example: sqrtpi = SQRTPI(23.44444)
DAX Trunc Math Function
Removes the fractional or decimal portion of a number to convert it to an integer.
Syntax: TRUNC(<number>, <num_digits>)
- number: The value you want to truncate.
- num_digits: A value indicating the truncation’s precision; if omitted, 0 (zero)
Example: truncate = TRUNC(45.0000345,6)