Add

Item1

Numeric

1

First item to add.

Item2

Numeric

1

Second item to add.

Return

Type is Float if either item is Float; otherwise, Decimal if either item is Decimal; otherwise, Integer.

Subtract

Item1

Numeric

1

Item from which to subtract.

Item2

Numeric

1

Item to subtract.

Return

Type is Float is either item is Float; otherwise, Decimal if either item is Decimal; otherwise, Integer.

Multiply

Item1

Numeric

1

First item to multiply.

Item2

Numeric

1

Second item to multiply.

Return

Type is Float if either item is Float; otherwise, Decimal if either item is Decimal; otherwise, Integer.

Divide

Item1

Numeric

1

Item to divide into.

Item2

Numeric

1

Item to divide by.

Return

Type is Float if either item is Float; otherwise, Decimal.

Power

Base

Numeric

1

Base to take to a power.

Exponent

Numeric

1

The exponent.

Return

Type is Float if either item is Float; otherwise, Decimal if either item is Decimal; otherwise, Integer.

Negate

Item

Numeric

1

Item to negate.

Return

Type is the same as the Item.

Mod

Item1

Integer

1

Item to divide into.

Item2

Integer

1

Item to divide by.

Return

Integer

The remainder of the division.

Equals

Item1

Boolean, DateTime, Integer, Decimal, Float, String, or EntityKey

1

First item to compare.

Item2

1

Second item to compare. Item1 and Item2 must have the same data type.

Return

Boolean

Indicates whether the items are the same.

NotEquals

Item1

Boolean, DateTime, Integer, Decimal, Float, String, or EntityKey

1

First item to compare.

Item2

1

Second item to compare. Item1 and Item2 must be the same data type.

Return

Boolean

Indicates whether the items are not the same.

GreaterThan

Item1

DateTime, Integer, Decimal, Float, or String

1

First item to compare.

Item2

1

Second item to compare. Item1 and Item2 must be the same data type.

Return

Boolean

Indicates whether the first item is greater than the second item.

GreaterThanOrEquals

Item1

DateTime, Integer, Decimal, Float, or String

1

First item to compare.

Item2

1

Second item to compare. Item1 and Item2 must be the same data type.

Return

Boolean

Indicates whether the first item is greater than or equal to the second item.

LessThan

Item1

DateTime, Integer, Decimal, Float, or String

1

First item to compare.

Item2

1

Second item to compare. Item1 and Item2 must be the same data type.

Return

Boolean

Indicates whether the first item is less than the second item.

LessThanOrEquals

Item1

DateTime, Integer, Decimal, Float, or String

1

First item to compare.

Item2

1

Second item to compare. Item1 and Item2 must be the same data type.

Return

Boolean

Indicates whether the first item is less than or equal to the second item.

And

Item1

Boolean

1

First condition.

Item2

Boolean

1

Second condition. If Item1 is false, Item2 is not evaluated.

Return

Boolean

If both Item1 and Item2 are true, the Return value is true.

Or

Item1

Boolean

1

First condition.

Item2

Boolean

1

Second condition. If Item1 is true, Item2 is not evaluated.

Return

Boolean

If either Item1 or Item2 are true, the Return value is true.

Not

Item

Boolean

1

Condition to negate.

Return

Boolean

If Item is false, the Return value is true.

Truncate

Item

Decimal or Float

1

Item to truncate.

Digits

Integer

1

Number of decimal digits to which the item should be truncated. For example, specify three to truncate the item after the third digit the right of the decimal.

Return

The Return value type is the same as the item being truncated.

Round

Item

Decimal or Float

1

Item to round.

Digits

Integer

1

Number of decimal digits that the item should be rounded to. For example, specify three to round the item to the third digit to the right of the decimal.

Return

The Return value type is the same as the item being rounded.

Integer

Item

Numeric or String

1

Item to cast. In Report Builder, Integer is listed on the Function tab as INT.

Return

Integer

The item cast as an integer. If the item is a numeric value, it is truncated. Note that the invariant locale is used to cast numeric strings. A period is the decimal separator. The comma used to separate thousands is not allowed.

Decimal

Item

Numeric or String

1

Item to cast.

Return

Decimal

The item cast as a decimal.

Float

Item

Numeric or String

1

Item to cast.

Return

Float

The item cast as a float.

String

Item

Numeric

1

Item to cast. In Report Builder, String is listed on the Function tab as TEXT.

Return

String

Item to cast as a string.

Length

String

String

1

String used to determine the length.

Return

Integer

Length of the string, specified as the number of characters within the string.

Find

String

String

1

String that is searched for a contained string.

Substring

String

1

The substring to search for.

Return

Integer

Position of the first instance of the substring within the string. If the substring is not found, 0 is returned.

Substring

String

String

1

String from which a substring is extracted.

Start

Integer

1

Start position within the string (1-based).

Length

Integer

1

Number of characters.

Return

String

The substring extracted from the string that contains the characters from Start to Start+Length.

Left

String

String

1

String from which to obtain the leftmost characters.

Length

Integer

1

Number of characters.

Return

String

The substring of the string that contains the characters from 1 to Length.

Right

String

String

1

String from which to obtain the rightmost characters.

Length

Integer

1

Number of characters.

Return

String

The substring of the string from Length(String)-Length+1 to Length(String).

Concat

String1

String

1

First string to concatenate.

String2

String

1

Second string to concatenate.

Return

String

The second string concatenated to the end of the first string.

Lower

String

String

1

String to convert to lowercase.

Return

String

String with all uppercase characters converted to lowercase.

Upper

String

String

1

String to convert to upper case.

Return

String

String with all lowercase characters converted to uppercase.

LTrim

String

String

1

String from which to trim leading spaces.

Return

String

String with all leading spaces removed.

RTrim

String

String

1

String from which to trim trailing spaces.

Return

String

String with trailing spaces removed.

Replace

String

String

1

String in which to replace all instances of one substring with another.

Find

String

1

The substring to search for.

Replace

String

1

The substring that replaces the Find string.

Return

String

String with all instances of Find replaced with Replace.

Date

Year

Integer

1

Year for the date.

Month

Integer

1

Month (1-12) for the date.

Day

Integer

1

Day (1-31) for the date which must be a valid day of the specified month and year.

Return

DateTime

A datetime with the given year, month, and day at 00:00:00.

DateTime

Year

Integer

1

Year for the date.

Month

Integer

1

Month (1-12) for the date.

Day

Integer

1

Day (1-31) for the date which must be a valid day of the specified month and year.

Hour

Integer

1

Hour (0-23) for the time.

Minute

Integer

1

Minute (0-59) for the time.

Second

Decimal

1

Second (0-60) for the time.

Return

DateTime

A datetime with the specified year, month, day, hour, minute, and second.

Time

DateTime

DateTime

1

Datetime from which to extract the time. Returns the hour, minute, and second from a datetime value.

Return

Time

Time from the datetime.

Year

DateTime

DateTime

1

Date from which the year is extracted.

Return

Integer

Year of the datetime.

Quarter

DateTime

DateTime

1

Date from which the quarter is extracted.

Return

Integer

Quarter (1-4) of the datetime.

Month

DateTime

DateTime

1

Date from which the month is extracted.

Return

Integer

Month (1-12) of the datetime.

Day

DateTime

DateTime

1

Date from which the day is extracted.

Return

Integer

Day (1-31) of the datetime.

Hour

DateTime

DateTime or Time

1

Date or time from which the hour is extracted.

Return

Integer

Hour (0-23) of the datetime.

Minute

DateTime

DateTime or Time

1

Date or time from which the minute is extracted.

Return

Integer

Minute (0-59) of the datetime.

Second

DateTime

DateTime or Time

1

Date or time from which the second is extracted.

Return

Integer

Second (0-60) of the datetime.

DayofYear

DateTime

DateTime

1

Date from which the day of the year is extracted.

Return

Integer

Day of year (1-366) of the datetime.

Week

DateTime

DateTime

1

Date from which the week is extracted.

Return

Integer

Week (1-53) of the datetime. The first day of the week is determined by the default first day of week associated with the culture of the semantic model.

DayofWeek

DateTime

DateTime

1

Date from which the day of the week is extracted.

Return

Integer

Day of week (1-7) of the datetime. Values start with Monday=1 through Sunday=7.

Date

DateTime

DateTime

1

Date from which to remove the time. In Report Builder, this Date function is listed on the Function tab as DATEONLY.

Return

Integer

Datetime with the time cleared (00:00:00).

Now

Return

DateTime

Current date/time. Now is a static function.

Today

Return

DateTime

Current date/time with the time cleared (00:00:00). Today is a static function.

DateDiff

Interval

String

1

The units used to specify the date difference. Must be one of the following: Year, Quarter, Month, Day, Hour, Minute, Second, or Week. Must be a literal. If the formula calculates the difference in a start time and an end time, the interval unit may only be HOUR, MINUTE, or SECOND.

Start

DateTime or Time

1

The start date or time.

End

DateTime or Time

1

The end date or time. Must have the same data type as the start time.

Return

Integer

The difference between the Start datetime and the End datetime, in the same units specified in the Interval. If the start datetime is after the end datetime, the result is negative.

DateAdd

Interval

String

1

The units used to specify the date or time addition. Must be one of the following: Year, Quarter, Month, Day, Hour, Minute, Second, or Week. Must be a literal. If the formula adds a number of time interval units to a start time, the interval unit may only be HOUR, MINUTE, or SECOND.

Number

Integer

1

The number of units of Interval to add to the date or time.

DateTime

DateTime or Time

1

The date or time to add to.

Return

DateTime

The date or time that is the result of adding the specified number of Interval units to the original date or time. Must have the same data type as the original start datetime or time field.

Sum

Items

Numeric

N

Items to sum.

Return

1

The sum of values for all of the items. The return value is the same data type as the items data type.

Avg

Items

Numeric

N

Items to average. In Report Builder, Avg is listed on the Function tab as AVERAGE.

Return

1

The average of non-null values of the Items. The data type is Decimal if the items are Decimal or Integer, otherwise, Float.

Max

Items

DateTime, Integer, Decimal, Float, or String

N

Items sorted to determine the maximum.

Return

1

The maximum of non-null values of the item. The return value is the same data type as the items data type.

Min

Items

DateTime, Integer, Decimal, Float, or String

N

Items sorted to determine the minimum.

Return

1

The minimum of non-null values of the Items. The return value is the same data type as the items data type.

Count

Items

Any

N

Items to count.

Return

Integer

1

The count of non-null values of the Items.

CountDistinct

Items

Any

N

Items to count. The data type of the items cannot be EntityKey.

Return

Integer

1

The count of distinct non-null values of the Items.

StDev

Items

Numeric

N

Items used to determine the standard deviation.

Return

Float

1

The standard deviation of non-null values of the Items.

StDevP

Items

Numeric

N

Items used to determine the population standard deviation.

Return

Float

1

The population standard deviation of non-null values of the Items.

Var

Items

Numeric

N

Items used to determine the variance.

Return

Float

1

The variance of non-null values of the Items.

VarP

Items

Numeric

N

Items used to determine the population variance.

Return

Float

1

The population of variance of non-null values of the Items.

GetUserID

Return

String

1

The user's user ID. GetUserID is a static function.

GetUserCulture

Return

Language

1

The user's language or locale. GetUserCulture is a static function.

Filter

Filter Items

Any

N

The values to be filtered. To filter items, use the Filter dialog box.

Filter Condition

Boolean

1

Indicates whether to include the corresponding instance.

Return

N

The returned value data type is the same as the Filter Items.

In

Item

Boolean, DateTime, Integer, Decimal, Float, String, or EntityKey

1

The item to check for set membership.

Set

N

This must be a Literal expression with no Path. The Item and the Set must have the same data type.

Return

Boolean

1

Indicates whether the item is in the set.

If

Condition

Boolean

1

Condition to test.

TrueCase

Any

1

Value to return if the condition is true. If the condition is false, TrueCase is not evaluated. Cannot be an EntityKey data type.

FalseCase

Any

1

Value to return if the condition is false. FalseCase must have the same value type as TrueCase. If the condition is true, FalseCase is not evaluated.

Return

1

The type must be the same as TrueCase.

Switch

Condition1

Boolean

1

Condition to test. Switch can have zero or more additional Condition/Value pairs:

Value1

Any

1

Value to return if condition1 is true.

Condition/N

Boolean

1

Condition to test. Not evaluated if any earlier condition is true.

Value/N

Boolean

1

Value to return if Condition/N is true. It must have the same data type as Value1. Not evaluated if Condition/N is not evaluated or if Condition/N is false.

Return

Boolean

1

Type is the same as the Value1. Returns Null if all conditions are false.

Evaluate

Expression

Any

N

The expression to evaluate. This function is used for controlling the entity context in which an expression is evaluated. For example, the following expression averages the prices for each distinct product the customer ordered:

Avg([customer->order->product]Price).

The following expression averages the prices (obtained from the product entity) for each order the customer placed:

Avg([customer->order]Evaluate([order->product]Price)).

There are multiple points along a path where evaluation location can control distinctness of an aggregate, multiple independent uses of Evaluate may be possible. The following is an example of multiple independent uses of Evaluate in a model where each product can be produced by multiple manufacturers:

Average([customer->order]Evaluate([order->product]Evaluate([product->manufacturer->city]population)))

Return

N

Type is the same as the expression.

Aggregate

Expression

Any

N

The aggregate expression to evaluate. This function is used to control the entity context in which an aggregation is evaluated. For example, consider a TotalSales attribute defined on an Order entity. The expression to show the total sales within the context of an order is simply: TotalSales. The expression to calculate the total sales within the context of a customer would be:

Aggregate([customer->order]TotalSales).

The expression argument must contain a non-anchored expression or one or more nested passthrough functions (which must take any data type), where the innermost passthrough argument has a non-anchored ExpressionNode. For example,

Aggregate([customer->order]Filter([order->product]Sum(UnitPrice),=(Shipped, "True")).

Return

1

Type is the same as the expression.