Original article (before wiki edits) was written by
Jeannine Takaki and JuanPablo Jofre and formatted by
Mary Browning, Microsoft SQL Server Technical Writers.

ABS

Returns the absolute value of a number.

The absolute value of a number is a real number, whole or decimal, without its sign. You can use the ABS function to ensure that only non-negative numbers are returned from expressions when nested in functions that require a positive number.

Syntax

ABS(<number>)

Number. The number for which you want the absolute value.

Returns a number (R8).

Example

The following example returns the absolute value of the difference between the list price and the dealer price, which you might use in a new calculated column,
DealerMarkup.

CEILING

Rounds a number up, to the nearest integer or to the nearest multiple of significance.

There are two CEILING functions in DAX, with the following differences:

The CEILING function emulates the behavior of the CEILING function in Excel.

The ISO.CEILING function follows the ISO-defined behavior for determining the ceiling value.

The two functions return the same value for positive numbers, but different values for negative numbers. When using a positive multiple of significance, both CEILING and ISO.CEILING round negative numbers upward (toward positive infinity). When using a negative
multiple of significance, CEILING rounds negative numbers downward (toward negative infinity), while ISO.CEILING rounds negative numbers upward (toward positive infinity).

The return type is usually of the same type of the significant argument, with the following exceptions:

If the number argument type is currency, the return type is currency.

If the significance argument type is Boolean, the return type is integer.

If the significance argument type is non-numeric, the return type is real.

Syntax

CEILING(<number>, <significance>)

Number. The number you want to round, or a reference to a column that contains numbers.

Significance. The multiple of significance to which you want to round. For example, to round to the nearest integer, type 1.

Returns a number rounded as specified.

Example

The following formula returns 4.45. This might be useful if you want to avoid using smaller units in your pricing. If an existing product is priced at $4.42, you can use CEILING to round prices up to the nearest unit of five cents.

=CEILING(4.42,0.05)

The following formula returns similar results as the previous example, but uses numeric values stored in the column,
ProductPrice.

INT

Rounds a number down to the nearest integer.

TRUNC and INT are similar in that both return integers. TRUNC removes the fractional part of the number. INT rounds numbers down to the nearest integer based on the value of the fractional part of the number. INT and TRUNC are different only when using negative
numbers: TRUNC(-4.3) returns -4, but INT(-4.3) returns -5 because -5 is the lower number.

Syntax

INT(<number>)

Number. The number you want to round down to an integer.

Returns a number (I8).

Example

The following expression rounds the value to 1. If you use the ROUND function, the result would be 2.

ISO.CEILING

Rounds a number up, to the nearest integer or to the nearest multiple of significance.

There are two CEILING functions in DAX, with the following differences:

The CEILING function emulates the behavior of the CEILING function in Excel.

The ISO.CEILING function follows the ISO-defined behavior for determining the ceiling value.

The two functions return the same value for positive numbers, but different values for negative numbers. When using a positive multiple of significance, both CEILING and ISO.CEILING round negative numbers upward (toward positive infinity). When using a negative
multiple of significance, CEILING rounds negative numbers downward (toward negative infinity), while ISO.CEILING rounds negative numbers upward (toward positive infinity).

The result type is usually the same type of the significance used as argument with the following exceptions:

If the first argument is of currency type then the result will be currency type.

If the optional argument is not included the result is of integer type.

If the significance argument is of Boolean type then the result is of integer type.

If the significance argument is non-numeric type then the result is of real type.

Syntax

ISO.CEILING(<number>[, <significance>])

Number. The number you want to round, or a reference to a column that contains numbers.

Significance. (optional) The multiple of significance to which you want to round. For example, to round to the nearest integer, type 1. If the unit of significance is not specified, the number is rounded up to the
nearest integer.

Returns a number rounded as specified.

Example: Positive Numbers

The following formula returns 4.45. This might be useful if you want to avoid using smaller units in your pricing. If an existing product is priced at $4.42, you can use ISO.CEILING to round prices up to the nearest unit of five cents.

Example

RAND

Returns a random number greater than or equal to 0 and less than 1, evenly distributed. The number that is returned changes each time the cell containing this function is recalculated.

In PowerPivot workbooks, recalculation depends on various factors, including whether the workbook is set to
Manual or Automatic recalculation mode, and whether data has been refreshed. This is different from Microsoft Excel, where you can control when RAND generates a new random number by turning off recalculation.

RAND and other volatile functions that do not have fixed values are not always recalculated. For example, execution of a query or filtering will usually not cause such functions to be re-evaluated. However, the results for these functions will be recalculated
when the entire column is recalculated. These situations include refresh from an external data source or manual editing of data that causes re-evaluation of formulas that contain these functions.

Moreover, RAND is always recalculated if the function is used in the definition of a measure.

Also, in such contexts the RAND function cannot return a result of zero, to prevent errors such as division by zero.

Syntax

RAND()

Returns a number (R8).

Example

To generate a random real number between two other numbers, you can use a formula like the following:

ROUNDDOWN

If num_digits is greater than 0 (zero), then the value in
number is rounded down to the specified number of decimal places.

If num_digits is 0, then the value in
number is rounded down to the nearest integer.

If num_digits is less than 0, then the value in
number is rounded down to the left of the decimal point.

Related Functions

ROUNDDOWN behaves like ROUND, except that it always rounds a number down. The INT function also rounds down, but with INT the result is always an integer, whereas with ROUNDDOWN you can control the precision of the result.

Syntax

ROUNDDOWN(<number>, <num_digits>)

Number. A real number that you want to round down.

Num_digits. The number of digits to which you want to round. Negative rounds to the left of the decimal point; zero to the nearest integer.

Returns a number (R8).

Example

The following example rounds 3.14159 down to three decimal places. The expected result is 3.141.

=ROUNDDOWN(3.14159,3)

The following example rounds the value of 31415.92654 down to 2 decimal places to the left of the decimal. The expected result is 31400.

ROUNDUP

If num_digits is greater than 0 (zero), then the number is rounded up to the specified number of decimal places.

If num_digits is 0, then number is rounded up to the nearest integer.

If num_digits is less than 0, then number is rounded up to the left of the decimal point.

Related Functions

ROUNDUP behaves like ROUND, except that it always rounds a number up.

Syntax

ROUNDUP(<number>, <num_digits>)

Number. A real number that you want to round up.

Num_digits. The number of digits to which you want to round. A negative value for
num_digits rounds to the left of the decimal point; if
num_digits is zero or is omitted,
number is rounded to the nearest integer.

Returns a number (R8).

Example

The following formula rounds Pi to four decimal places. The expected result is 3.1416.

=ROUNDUP(PI(),4)

Example: Decimals as Second Argument

The following formula rounds 1.3 to the nearest multiple of 0.2. The expected result is 1.4.

=ROUNDUP(1.3,0.2)

Example: Negative Number as Second Argument

The following formula rounds the value in the column, FreightCost, with the expected results shown in the following table.

=ROUNDUP([Values],-1)

When num_digits is less than zero, the number of places to the left of the decimal sign is increased by the value you specify.

Example

SUMX

Returns the sum of an expression evaluated for each row in a table.

The SUMX function takes as its first argument a table, or an expression that returns a table. The second argument is a column that contains the numbers you want to sum, or an expression that evaluates to a column.

Only the numbers in the column are counted. Blanks, logical values, and text are ignored.

Syntax

Table. The table containing the rows for which the expression will be evaluated.

Expression. The expression to be evaluated for each row of the table.

Returns a number (R8 or I8: depends on the data type of the expression being added).

Example

The following example first filters the table, InternetSales, on the expression,
ShippingTerritoryID = 5, and then returns the sum of all values in the column, Freight. In other words, the expression returns the sum of freight charges for only the specified sales area.

TRUNC

Truncates a number to an integer by removing the decimal, or fractional, part of the number.

TRUNC and INT are similar in that both return integers. TRUNC removes the fractional part of the number. INT rounds numbers down to the nearest integer based on the value of the fractional part of the number. INT and TRUNC are different only when using negative
numbers: TRUNC(-4.3) returns -4, but
INT(-4.3) returns -5 because -5 is the smaller number.

Syntax

TRUNC(<number>,<num_digits>)

Number. The number you want to truncate.

Num_digits. A number specifying the precision of the truncation; if omitted, 0 (zero).