MDX Function List

These are the functions implemented in the current MDX for Kylin release.

Name Description&Usage
() Brace operator constructs a Tuple.
<Tuple>: (<Member>[ ,<Member>...])

([Date].[Day].&[1917-02-09],[Product].[Color].&[Blue])
{} Brace operator constructs a Set.
<Set>: {<Member>[ ,<Member>...]}
<Set>: {<Tuple>[ ,<Tuple>...]}

Example 1：{[Customer].[BirthDate].&[1917-02-09]}
Example 2：{[Customer].[BirthDate].[BirthDate] .Members}
Example 3：{([Customer].[BirthDate].&[1917-02-09],[Product].[Color].&[Blue])}
* Multiplies two numbers.
<Numeric>: <Numeric> * <Numeric>

[Measures].[Count]*5
* Returns the cross product of two sets.
<Set>: <Set> * <Set>
<Set>:<Member> * <Set>
<Set>:<Set> * <Member>
<Set>:<Member> * <Member>

Example 1：{[Customer].[BirthDate].[BirthDate] .Members} *{[Customer].[Education].[Education].Members}
Example 2：{[Customer].[BirthDate].&[1917-02-19]}*{[Customer].[Education].[Education] .Members}
Example 3：{[Customer].[BirthDate].[BirthDate] .Members} * {[Customer].[Education].&[Bachelors]}
Example 4：{[Customer].[BirthDate].&[1917-02-19]}*{[Customer].[Education].&[Bachelors]}
+ Adds two numbers
<Numeric>: <Numeric> + <Numeric>

[Measures].[Count]+2
- Subtracts two numbers.
<Numeric>: <Numeric> - <Numeric>

[Measures].[Count]-2
- Returns the negative of a number.
<Numeric>: - <Numeric Expression> -

[Measures].[Count]
/ Divides two numbers.
<Numeric>: <Numeric> / <Numeric>

Example 1：2/1
Example 2：[Measures].[Count]/5
< Returns whether an expression is less than another.
<Boolean>: <Numeric> < <Numeric>
<Boolean>: <String> < <String>

Example 1：[Measures].[A] < [Measures].[B]
Example 2：'kylin' < 'kylingence'
<= Returns whether an expression is less than or equal to another.
<Boolean>: <Numeric> <= <Numeric>
<Boolean>: <String> <= <String>

Example 1：[Measures].[A] <= 10000
Example 2：'kylin' <= 'kylingence'
<> Returns whether two expressions are not equal.
<Boolean>: <Numeric> <> <Numeric>
<Boolean>: <String> <> <String>

Example 1：[Measures].[A] <> 10000
Example 2：'kylin' <> 'kylingence'
= Returns whether two expressions are equal.
<Boolean>: <Numeric> = <Numeric>
<Boolean>: <String> = <String>

Example 1：1 = 2
Example 2：'kylin' = 'kylingence'
> Returns whether an expression is greater than another.
<Boolean>: <Numeric> > <Numeric>
<Boolean>: <String> > <String>

Example 1：1 > 2
Example 2：'kylin' > 'kylingence'
>= Returns whether an expression is greater than or equal to another.
<Boolean>: <Numeric> >= <Numeric>
<Boolean>: <String> >= <String>

Example 1：1 > 2
Example 2：'kylin' > 'kylingence'
AND Returns the conjunction of two conditions.
<Boolean>: <Boolean> AND <Boolean>

([Measures].[Count]>10000) AND (1=2)
Abs Returns a value of the same type that is passed to it specifying the absolute value of a number.
<Numeric>: Abs(<Numeric>)

Abs(200.1-300)
Acos Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is Arg1. The returned angle is given in radians in the range 0 (zero) to pi.
<Numeric>: Acos(<Numeric>)

Acos(0.540302)+acos (-1.000000)
Acosh Returns the inverse hyperbolic cosine of a number. Number must be greater than or equal to 1. The inverse hyperbolic cosine is the value whose hyperbolic cosine is Arg1, so Acosh(Cosh(number)) equals Arg1.
<Numeric>: Acosh(<Numeric>)

Acosh(1)+Acosh(10)
Aggregate Returns a calculated value using the appropriate aggregate function, based on the context of the query.
<Numeric>: Aggregate(<Set>) <Numeric>: Aggregate(<Set>, <Numeric>)

Example 1：Aggregate({[Date].[CalendarYear].&[2014],[Date].[CalendarYear].&[2013]})
Example 2：Aggregate({[Date].[CalendarYear].&[2014],[Date].[CalendarYear].&[2013]},[Measures].[Count])
AllMembers Returns a set that contains all members ,including calculated members,of the specified object.
<Set>: <Dimension>.AllMembers
<Set>: <Hierarchy>.AllMembers
<Set>: <Level>.AllMembers

Example 1：[Date].[CalendarSemester].[CalendarSemester] .Allmembers
Example 2：[Date].[Due Fiscal Date-Hierarchy].Allmembers
Example 3：[Date].[Due Fiscal Date-Hierarchy].[FiscalYear].Allmembers
Asc Returns an Integer representing the character code corresponding to the first letter in a string.
<Integer>: Asc(<String>) Asc("kylingence")
AscB See Asc.
<Integer>: AscB(<String>) AscB("kylingence")
AscW See Asc.
<Integer>: AscW(<String>)

AscW("kylingence")
Ascendants Returns the set of the ascendants of a specified member.
<Set>: Ascendants(<Member>)

Ascendants([Date].[Due Fiscal Date-Hierarchy].[FiscalYear].&[2013].&[1]
Asin Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is Arg1. The returned angle is given in radians in the range -pi/2 to pi/2.
<Numeric>: Asin(<Numeric>)

Asin(-0.5)
Asinh Returns the inverse hyperbolic sine of a number. The inverse hyperbolic sine is the value whose hyperbolic sine is Arg1, so Asinh(Sinh(number)) equals Arg1.
<Numeric>: Asinh(<Numeric>)

Asinh(-5)
Atan2 Returns the arctangent, or inverse tangent, of the specified x- and y-coordinates. The arctangent is the angle from the x-axis to a line containing the origin (0, 0) and a point with coordinates (x_num, y_num). The angle is given in radians between -pi and pi, excluding -pi
<Numeric>: Atan2(<Numeric>, <Numeric>)

Atan2 (-7,7)
Atanh Returns the inverse hyperbolic tangent of a number. Number must be between -1 and 1 (excluding -1 and 1).
<Numeric>: Atanh(<Numeric>)

Atanh(0.76159416)
Atn Returns a Double specifying the arctangent of a number.
<Numeric>: Atn(<Numeric>)

Atn (90)
Avg Returns the average value of a numeric expression evaluated over a set.
<Numeric>: Avg(<Set>) <Numeric> Avg(<Set>, <Numeric>)

Example 1：Avg({[Date].[CalendarQuarter].[CalendarQuarter].members})
Example 2：Avg({[Date].[CalendarQuarter].[CalendarQuarter].members},[Measures].[Count])
BottomPercent The BottomPercent function calculates the sum of the specified numeric expression evaluated over a specified set. The function then returns the elements with the lowest values whose cumulative percentage of the total summed value is at least the specified percentage. This function returns the smallest subset of a set whose cumulative total is at least the specified percentage.
<Set>: BottomPercent(<Set>, <Numeric>, <Numeric>)

BottomPercent({[Product].[Color].[Color].members},60,[Measures].[Count])
BottomSum Sorts a set and returns the bottom N elements whose cumulative total is at least a specified value.
<Set>: BottomSum(<Set>, <Numeric>, <Numeric>)

BottomSum({[Product].[Color].[Color].members},5220,[Measures].[Count])
CBool Returns an expression that has been converted to a Variant of subtype Boolean.
<Boolean>: CBool(<Value>)

CBool(100)
CDate Returns an expression that has been converted to a Variant of subtype Date.
<DateTime>: CDate(<Value>)

CDate("2020-07-25 17:44:10")
CDbl Returns an expression that has been converted to a Variant of subtype Double.
<Numeric>: CDbl(<Value>)

CDbl(234.456784)
CInt Returns an expression that has been converted to a Variant of subtype Integer.
<Integer>: CInt(<Value>)

CInt(234.121231231)
Cache Evaluates and returns its sole argument, applying statement-level.cachingCache(<<Expression>>)

Cache([Measures].[Count])
Caption Returns the caption of an object.
<String>: <Dimension>.Caption
<String>: <Hierarchy>.Caption
<String>: <Level>.Caption
<String>: <Member>.Caption

Example 1：[Customer].[EmailAddress].Caption
Example 2：[Date].[Due Calendar Date-Hierarchy].Caption
Example 3：[Date].[Due Calendar Date-Hierarchy].[CalendarYear].Caption
Example 4：[Product].[Class].&[M].caption
Cast Converts values to another type.
<Type>: Cast(<Expression> AS <Type>)

Cast(1+2 AS STRING)
Children Returns the children of a member.
<Set>: <Member>.Children

[Date].[Due Calendar Date-Hierarchy].[CalendarYear].&[2010].children
Chr Returns a String containing the character associated with the specified character code.
<String>: Chr(<Integer>)

chr(48)
ChrB See Chr.
<String>: ChrB(<Integer>)

ChrB(48)
ChrW See Chr.
<String>: ChrW(<Integer>)

ChrW(48)
ClosingPeriod Returns the last descendant of a member at a level.
<Member>: ClosingPeriod()
<Member>: ClosingPeriod(<Level>)
<Member>: ClosingPeriod(<Level>, <Member>)
<Member>: ClosingPeriod(<Member>)

Example 1：[Customer].[person-Hierarchy].[Education].closingperiod
Example 2：ClosingPeriod([Date].[Due Fiscal Date-Hierarchy].[FiscalYear])
Example 3：ClosingPeriod([ Date].[Due Fiscal Date-Hierarchy].[FiscalYear] ,[Date].[Due Fiscal Date-Hierarchy].[FiscalYear].&[2010])
Example 4：ClosingPeriod([Date].[Due Fiscal Date-Hierarchy].[FiscalYear].&[2010])
CoalesceEmpty Coalesces an empty cell value to a different value. All of the expressions must be of the same type (number or string).
CoalesceEmpty(<Value Expression>[,<Value Expression>...])

CoalesceEmpty(88)
Correlation Returns the correlation of two series evaluated over a set.
<Numeric>: Correlation(<Set>, <Numeric>)
<Numeric>: Correlation(<Set>, <Numeric>, <Numeric>)

Example 1：Correlation({[Customer].[Education].&[Bachelors],[Customer].[Education].&[High School]},[Measures].[Count])
Example 2：Correlation({[Customer].[Education].&[Bachelors],[Customer].[Education].&[High School]},[Measures].[SalesAmout],[Measures].[Count])
Cos Returns a Double specifying the cosine of an angle.
<Numeric>: Cos(<Numeric>)

Cos(60*Pi()/180)
Cosh Returns the hyperbolic cosine of a number.
<Numeric>: Cosh(<Numeric>)

Cosh(4)
Count Returns the number of tuples in a set, empty cells included unless the optional EXCLUDEEMPTY flag is used.
<Numeric>: Count(<Set>) <Numeric>: Count(<Set>, <Symbol>)

Example 1：Count({[Product].[Color].[Color].members})
Example 2：Count({[Product].[Color].[Color].members},EXCLUDEEMPTY)
Count Returns the number of tuples in a set including empty cells.
<Integer>: <Set>.Count

[Product].[Class].[Class].members.Count
Covariance Returns the covariance of two series evaluated over a set (biased).
<Numeric>: Covariance(<Set>, <Numeric>)
<Numeric>: Covariance(<Set>, <Numeric>, <Numeric>)

Example 1：Covariance([Product].[Class].[Class].Members, [Measures].[Product Standard Cost])
Example 2：Covariance([Product].[Class].[Class].Members, [Measures].[Product Standard Cost], [Measures].[Freight])
CovarianceN Returns the covariance of two series evaluated over a set (unbiased).
<Numeric>: CovarianceN(<Set>, <Numeric>)
<Numeric>: CovarianceN(<Set>, <Numeric>, <Numeric>)

Example 1：Covariancen([Product].[Class].[Class].Members, [Measures].[Product Standard Cost])
Example 2：Covariancen([Product].[Class].[Class].Members, [Measures].[Product Standard Cost], [Measures].[Freight])
Crossjoin Returns the cross product of two sets.
<Set>: Crossjoin(<Set>, <Set>)

Crossjoin([Product].[Color].[Color].members,[Product].[Size].[Size].members)
CurrentDateMember Returns the exact member within the specified dimension corresponding to the current date, in the format specified by the format parameter. If there is no such date, returns the NULL member. Format strings are the same as used by the MDX Format function, namely the Visual Basic format strings.
<Member>: CurrentDateMember(<Hierarchy>, <String>)

CurrentDateMember([Date].[Calendar-Hierarchy],'yyyymmdd')
CurrentDateMember Returns the closest or exact member within the specified dimension corresponding to the current date, in the format specified by the format parameter. Format strings are the same as used by the MDX Format function, namely the Visual Basic format strings.
<Member>: CurrentDateMember(<Hierarchy>, <String>, <Symbol>)

CurrentDateMember([Date].[Order Fiscal Date-Hierarchy],"20050402",BEFORE)
CurrentDateString Returns the current date formatted as specified by the format parameter.
<String>: CurrentDateString(<String>)

CurrentDateString('yyyymmdd')
CurrentMember Returns the current member along a dimension during an iteration.
<Member>: <Dimension>.CurrentMember

[Product].[Class].CurrentMember
CurrentMember Returns the current member along a hierarchy during an iteration.
<Member>: <Hierarchy>.CurrentMember

[Date].[Order Fiscal Date-Hierarchy].CurrentMember
DDB Returns a Double specifying the depreciation of an asset for a specific time period using the double-declining balance method or some other method you specify.
<Numeric>: DDB(<Numeric>, <Numeric>, <Numeric>, <Numeric>, <Numeric>)
Parameter1:The initial cost of the asset.
Parameter2:The value at the end of the depreciation. This value can be 0.
Parameter3:The number of periods over which the asset is being depreciated
Parameter4:The period for which you want to calculate the depreciation. Period must use the same units as life.
Parameter5:The rate at which the balance declines. If factor is omitted, it is assumed to be 2 (the double-declining balance method).

Example 1：DDB(24000,3000,10,2)
Example 2：DDB(24000,3000,10,2,1.5)
DataMember Returns the system-generated data member that is associated with a nonleaf member of a dimension.
<Member>: <Member>.DataMember

[Customer].[CustomerKey].&[11001].DataMember
Date Returns a Variant (Date) containing the current system date.
<DateTime>: Date() Date()
DateAdd Returns a Variant (Date) containing a date to which a specified time interval has been added.
<DateTime>: DateAdd(<String>, <Numeric>, <DateTime>)

Example 1：DateAdd("h",1,date())
Example 2：DateAdd("h",1,[DUEDATE].[DateKey].&[2011-11-23])
DateDiff Returns a Variant (Long) specifying the number of time intervals between two specified dates.
<Numeric>: DateDiff(<String>, <DateTime>, <DateTime>, <Integer>, <Integer>)
String Type：yyyy,q,m,y,d,ww,w,hh,n,s,ms,mcs,ns
Parameter4:A constant that specifies the first day of the week. If not specified, Sunday is assumed.
Parameter5:A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January
1 occurs.

Example 1：DateDiff("yyyy",Date(),Date())
Example 2：DateDiff("yyyy",[Date].[DateKey].&[2011-11-23],
[Date].[DateKey].&[2012-11-24])
Example 3：DateDiff("yyyy",Date(),Date(),1)
Example 4：DateDiff("yyyy",Date(),Date(),1,1)
DatePart Returns a Variant (Integer) containing the specified part of a given date.
<Integer>: DatePart(<String>, <DateTime>, <Integer>, <Integer>)
Parameter3:A constant that specifies the first day of the week. If not
specified, Sunday is assumed.
Parameter4:A constant that specifies the first week of the year. If not
specified, the first week is assumed to be the week in which January
1 occurs.

Example 1：DatePart("yyyy",[Date].[DateKey].&[2011-11-23])
Example 2：DatePart("yyyy",[Date].[DateKey].&[2011-11-23],1)
Example 3：DatePart("yyyy",[Date].[DateKey].&[2011-11-23],1,1)
DateSerial Returns a Variant (Date) for a specified year, month, and day.
<DateTime>: DateSerial(<Integer>, <Integer>, <Integer>)

DateSerial(2020,3,5)
DateValue Returns a Variant (Date).
<DateTime>: DateValue(<DateTime>)

DateValue(CDate('2020-08-27'))
Day Returns a Variant (Integer) specifying a whole number between 1 and 31, inclusive, representing the day of the month.
<Integer>: Day(<DateTime>)

Day(CDate('2010-09-23'))
DefaultMember Returns the default member of an object.
<Member>: <Dimension>.DefaultMember
<Member>: <Hierarchy>.DefaultMember

Example 1：[Product].[Class].DefaultMember
Example 2：[Date].[Order Fiscal Date-Hierarchy].DefaultMember
Degrees Converts radians to degrees.
<Numeric>: Degrees(<Numeric>) Degrees(0.5*Pi())
Descendants Returns the set of descendants of a member at a specified level,optionally including or excluding descendants in other levels.
<Set>: Descendants(<Member>)
<Set>: Descendants(<Member>, <Level>)
<Set>: Descendants(<Member>, <Level>,<Symbol>)
<Set>: Descendants(<Member>, <Numeric>)
<Set>: Descendants(<Member>, <Numeric Expression>, <Symbol>)
<Set>: Descendants(<Member>, <Empty>, <Symbol>)

Example 1：Descendants([Date].[Due Calendar Date-Hierarchy].[CalendarYear].&[2010])
Example 2：Descendants([Date].[Due Calendar Date-Hierarchy].[CalendarYear].&[2010], [Date].[Due Calendar Date-Hierarchy].[DateKey])
Example 3：Descendants([Date].[Due Calendar Date-Hierarchy].[CalendarYear].&[2010],[Date].[Due Calendar Date-Hierarchy].[CalendarYear],SELF)
Dimension Returns the dimension that contains a specified object.
<Dimension>: <Dimension>.Dimension
<Dimension>: <Hierarchy>.Dimension
<Dimension>: <Level>.Dimension
<Dimension>: <Member>.Dimension

Example 1：[Date].[CalendarYear].[CalendarYear].Dimension
Example 2：[Date].[Due Calendar Date-Hierarchy].Dimension
Example 3：[Date].[Due Fiscal Date-Hierarchy].[FiscalYear].Dimension
Example 4：[Date].[CalendarYear].&[2010].Dimension
Dimensions Returns the dimension whose zero-based position within the cube is specified by a numeric expression.
<Dimension>: Dimensions(<Numeric>)

Dimensions(2)
Dimensions Returns the dimension whose name is specified by a string.
<Dimension>: Dimensions(<String>)

Dimensions('[Customer].[AddressLine]')
Distinct Eliminates duplicate tuples from a set.
<Set>: Distinct(<Set>)

Distinct({[Date].[FiscalYear].&[2005],[Date].[FiscalYear].&[2005],[Date].[FiscalYear].&[2006]})
DrilldownLevel Drills down the members of a set, at a specified level, to one level below. Alternatively, drills down on a specified dimension in the set.According to the multi-level hierarchical data structure,you can explicitly choose a level at which to drill down. There are two mutually exclusive ways to specify the level. The first approach is to set the level_expression argument using an MDX expression that returns the level, An alternative approach is to specify the index argument, using a numeric expression that specifies the level by number.
<Set>: DrilldownLevel(<Set>)
<Set>: DrilldownLevel(<Set>, <Level>)
<Set>: DrilldownLevel(<Set>, <Empty>, <Numeric>)
<Set>: DrilldownLevel(<Set>)
<Set>: DrilldownLevel(<Set>, <Level>)
<Set>: DrilldownLevel(<Set>, <Empty>, <Numeric>)
Expression2:Set Level_expression
Expression3:Set Index

Example 1：DrillDownLevel([Date].[Due Calendar Date-Hierarchy].[CalendarQuarter].Members)
Example 2：DrilldownLevel([Date].[Due Calendar Date-Hierarchy].[CalendarQuarter].Members,[Date].[Due Calendar Date-Hierarchy].[EnglishMonthName])
Example 3：DrilldownLevel([Date].[Due Calendar Date-Hierarchy].[CalendarYear].Members,,1)
DrilldownMember Drills down the members in a set that are present in a second specified set.If RECURSIVE is specified, the function continues to recursively compare the members of the result set against the second set, retrieving the children for each member in the result set that is also present in the second set until no more members from the result set can be found in the second set.
<Set>: DrilldownMember(<Set>, <Set>)
<Set> DrilldownMember(<Set>, <Set>, <Symbol>)
Symbol Type：RECURSIVE

Example 1：DrillDownMember([Date].[Due Fiscal Date-Hierarchy].Members,[Date].[Due Calendar Date-Hierarchy].Members)
Example 2：DrilldownMember([Date].[Due Fiscal Date-Hierarchy].Members,[Date].[Due Calendar Date-Hierarchy].Members,RECURSIVE)
Except Finds the difference between two sets, optionally retaining duplicates.If ALL is specified, the function retains duplicates found in the first set; duplicates found in the second set will still be removed. The members are returned in the order they appear in the first set.
<Set>: Except(<Set>, <Set>)
<Set>: Except(<Set>, <Set>, <Symbol>)
Symbol Type：ALL

Except({[Date].[CalendarYear].&[2005],[Date].[CalendarYear].&[2006]},{[Date].[CalendarYear].&[2005]})
Exclude Restrict the level of detail of a measure,For example, the expression is Exclude( [Product], [Measures].[Qty] ), which means that the calculation of [Measures].[Qty] will not be affected by the filter conditions of the dimensions in the dimension table [Product].
<Value> : Exclude( [<Dimension> | <Hierarchy>, ...]
<Measure Expression>)

Exclude([Date].[DueFiscalDate-Hierarchy],[Measures].[Count])
Exists Returns the the set of tuples of the first set that exist with one or more tuples of the second set.
<Set>: Exists(<Set>, <Set>)

Exists({[Customer].[Education].&[Bachelors]},{[Customer].[Education].&[Bachelors],[Customer].[Education].&[High School]})
Exp Returns a Double specifying e (the base of natural logarithms) raised to a power.
<Numeric> Exp(<Numeric>)

[Measures].[Count]*exp(1)
Extract Returns a set of tuples from extracted dimension elements. The opposite of Crossjoin.
<Set>: Extract(<Set>, <Dimension>[, <Dimension>...])

Extract( [Customer].[Education].[Education].Members * {[Date].[CalendarYear].&[2011],[Date].[CalendarYear].&[2013]}, [Customer].[Education] )
FV Returns a Double specifying the future value of an annuity based on periodic, fixed payments and a fixed interest rate.
<Numeric>: FV(<Numeric>, <Numeric>, <Numeric>, <Numeric>, <Boolean>)
Parameter1:The interest rate per period.
Parameter2:The total number of payment periods in an annuity.
Parameter3:The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.

Example 1：FV(0.12,4,10000)
Example 2：FV(0.01,48,1000,1)
Example 3：FV(0.01,48,1000,1,1)
Filter Returns the set resulting from filtering a set based on a search condition.
<Set>: Filter(<Set>,<Boolean>)

Filter([Customer].[CustomerKey].[CustomerKey].Members,[Measures].[Count]>6)
FirstChild Returns the first child of a member.
<Member>: <Member>.FirstChild

[Date].[Due Calendar Date-Hierarchy].[CalendarYear].&[2013].FirstChild
FirstQ Returns the 1st quartile value of a numeric expression evaluated over a set.
<Numeric>: FirstQ(<Set>)
<Numeric>: FirstQ(<Set>, <Numeric>)

FirstQ([Date].[CalendarYear].[CalendarYear].Members,[Measures].[SalesAmout])
FirstSibling Returns the first child of the parent of a member.
<Member>: <Member>.FirstSibling

[Date].[CalendarYear].&[2009].FirstSibling
Fix Returns the integer portion of a number. If negative, returns the negative number greater than or equal to the number.
<Integer>: Fix(<Numeric>)

[Measures].[Count]*fix(1.3)
Fixed Restrict the level of detail of a measure,For example, the expression is Fixed( [Product], [Measures].[Qty] ), which means that the calculation of [Measures].[Qty] will not be affected by the filter conditions of the dimensions in the dimension table [Product].
<Value> : Fixed( [<Dimension> | <Hierarchy>, ...]
<Measure Expression>)

Fixed([Product].[Class].[Class],[Measures].[Count])
Format Formats a number or date to a string.
<String>: Format(<Member>, <String>)
<String>: Format(<Numeric>, <String>)
<String>: Format(<DateTime>, <String>)

format([Measures].[ Count],"#,##0.00")
FormatCurrency Returns an expression formatted as a currency value using the currency symbol defined in the system control panel.
<String>: FormatCurrency(<Value>, <Integer>, <Integer>,
<Integer>, <Integer>)
Parameter1:Expression to be formatted.
Parameter2:Numeric value indicating how many places to the right of the decimal are displayed. Default value is –1
Parameter3:Tristate constant that indicates whether or not a leading zero is displayed for fractional values. See Settings section for values.
Parameter4:Tristate constant that indicates whether or not to place negative values within parentheses. See Settings section for values.
Parameter5:Tristate constant that indicates whether or not numbers are grouped using the group delimiter specified in the computer's regional settings. See Settings section for values.
-2 = TristateUseDefault - Use the setting from the computer's regional settings.
-1 = TristateTrue - True
0 = TristateFalse - False

Example 1：FormatCurrency([Measures].[Unitprice])
Example 2：FormatCurrency([Measures].[Unitprice],2)
Example 3：FormatCurrency([Measures].[Unitprice],2,-1)
Example 4：FormatCurrency([Measures].[Unitprice],2,-1,-2)
Example 5：FormatCurrency([Measures].[Unitprice],2,-1,-2,-1)
FormatDateTime Returns an expression formatted as a date or time.
<String>: FormatDateTime(<DateTime>, <Integer>)

Example 1：FormatDateTime(Date())
Example 2：FormatDateTime(Date(),2)
FormatNumber Returns an expression formatted as a number.
<String>: FormatNumber(<Value>, <Integer>, <Integer>,
<Integer>, <Integer>)
Parameter1:Expression to be formatted.
Parameter2:Numeric value indicating how many places to the right of the decimal are displayed. Default value is –1
Parameter3:Tristate constant that indicates whether or not a leading zero is displayed for fractional values. See Settings section for values.
Parameter4:Tristate constant that indicates whether or not to place negative values within parentheses. See Settings section for values.
Parameter5:Tristate constant that indicates whether or not numbers are grouped using the group delimiter specified in the computer's regional settings. See Settings section for values.
-2 = TristateUseDefault - Use the setting from the computer's regional settings.
-1 = TristateTrue - True
0 = TristateFalse - False

Example 1：FormatNumber([Measures].[Freight])
Example 2：FormatNumber([Measures].[Freight],2)
Example 3：FormatNumber([Measures].[Unitprice],2,-1)
Example 4：FormatNumber([Measures].[Unitprice],2,-1,-2)
Example 5：FormatNumber([Measures].[Unitprice],2,-1,-2,-1)
FormatPercent Returns an expression formatted as a percentage (multipled by 100) with a trailing % character.
<String>: FormatPercent(<Value>, <Integer>, <Integer>,
<Integer>, <Integer>)
Parameter1:Expression to be formatted.
Parameter2:Numeric value indicating how many places to the right of the decimal are displayed. Default value is –1
Parameter3:Tristate constant that indicates whether or not a leading zero is displayed for fractional values. See Settings section for values.
Parameter4:Tristate constant that indicates whether or not to place negative values within parentheses. See Settings section for values.
Parameter5:Tristate constant that indicates whether or not numbers are grouped using the group delimiter specified in the computer's regional settings. See Settings section for values.
-2 = TristateUseDefault - Use the setting from the computer's regional settings.
-1 = TristateTrue - True
0 = TristateFalse - False

Example 1：FormatPercent([Measures].[Count]/60398)
Example 2：FormatPercent([Measures].[Count]/60398,2)
Example 3：FormatPercent([Measures].[Count]/61000,2,-1)
Example 4：FormatPercent([Measures].[Count]/61000,2,-1,-2)
Example 5：FormatPercent([Measures].[Count]/61000,2,-1,-2,-1)
Generate Applies a set to each member of another set and joins the resulting sets by union.If ALL is specified, the function retains duplicates in the resulting set.
<Set>: Generate(<Set>, <Set>)
<Set>: Generate(<Set>, <Set>, <Symbol>)
Symbol Type：ALL

Generate( [Date].[CalendarYear].[CalendarYear].Members ,
TopCount( [Date].[CalendarYear].CurrentMember *
[Product].[ProductLine].[ProductLine].Members ,3, [Measures].[SalesAmout))
Generate Applies a set to a string expression and joins resulting sets by string concatenation.
<String>: Generate(<Set>, <String>)
<String>: Generate(<Set>, <String>, <String>)

Example 1：Generate([Date].[CalendarYear].[CalendarYear].Members,"AND")
Example 2：Generate( [Date].[CalendarYear].[CalendarYear].Members, [Date].[CalendarYear].CurrentMember.Name,"AND")
Head Returns the first specified number of elements in a set.
<Set>: Head(<Set>)
<Set>: Head(<Set>, <Numeric>)

Example 1：Head([Customer].[CustomerKey].[CustomerKey].Members)
Example 2：Head([Customer].[CustomerKey].[CustomerKey].Members,3)
Hex Returns a String representing the hexadecimal value of a number.
<String>: Hex(<Value>)

Hex([Measures].[Count])
Hierarchize Orders the members of a set in a hierarchy.If POST is specified, the Hierarchize function sorts the members in a level using a post-natural order. In other words, child members precede their parents.
<Set>: Hierarchize(<Set>)
<Set>: Hierarchize(<Set>, <Symbol>)
Symbol Type：POST

Hierarchize({[Customer].[Education].&[High School],[Customer].[Education].&[Bachelors]})
Hierarchy Returns an object's hierarchy.
<Hiearchy>: <Level>.Hierarchy
<Hiearchy>: <Member>.Hierarchy

Example 1：[Date].[Due Fiscal Date-Hierarchy].[FiscalYear].Hierarchy
Example 2：[Date].[Due Fiscal Date-Hierarchy].[FiscalYear].&[2010].Hierarchy
Hour Returns a Variant (Integer) specifying a whole number between 0 and 23, inclusive, representing the hour of the day.
<Integer>: Hour(<DateTime>)

Hour(Cdate('2020-08-12 09:30:45'))
IIf Returns one of two objects determined by a logical test.
<Tuple>: IIf(<Boolean>, <Tuple>, <Tuple>)
<Dimension>: IIf(<Boolean>, <Dimension>, <Dimension>)
<Hierarchy>: IIf(<Boolean>, <Hierarchy>, <Hierarchy>)
<Level>: IIf(<Boolean>, <Level>, <Level>)
<Boolean>: IIf(<Boolean>, <Boolean>, <Member>: IIf(<Boolean>, <Member>, <Member>)
<Numeric>: IIf(<Boolean>, <Numeric>, <Numeric>)
<Set>: IIf(<Boolean>, <Set>, <Set>)
<String>: IIf(<Boolean>, <String>, <String>)

Example 1：IIF([Measures].[Count]>80000, ([Customer].[Education].&[Bachelors].[Date].[CalendarYear].&[2011]), ([Customer].[Education].&[High School].[Date].[CalendarYear].&[2011]))
Example 2：IIF([Measures].[Count]>80000,[Customer].[CustomerKey],[Customer].[FirstName])
Example 3：IIF([Measures].[Count]>80000,[Date].[Due Fiscal Date-Hierarchy],[Customer].[person-Hierarchy])
Example 4：IIF([Measures].[Count]>80000, [Date].[Due Fiscal Date-Hierarchy].[FISCALYEAR], [Date].[Due Fiscal Date-Hierarchy].[DateKey])
Example 5：IIF([Measures].[Count]>80000,[Measures].[SalesAmout]<10000,[measures].[salesamout]>10000)
Example 6：IIF([Measures].[Count]>80000, [Customer].[Education].[Bachelors], [Customer].[Education].&[High School])
Example 7：IIF([Measures].[Count]>80000,[Measures].[Unitprice]/3,[Measures].[Unitprice]/4)
Example 8：IIF([Measures].[Count]>80000, {[Customer].[Education].&[Bachelors]}, {[Customer].[Education].&[Bachelors],[Customer].[Education].&[High School]})
Example 9：IIF([Measures].[SalesAmout]>10000, "Sales Are High", "Sales Are Low")
IPmt Returns a Double specifying the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.
<Numeric>: IPmt(<Numeric>, <Numeric>, <Numeric>, <Numeric>, <Numeric>, <Boolean>)
Parameter1:The interest rate per period.
Parameter2:The period for which you want to find the interest .
Parameter3:The total number of payment periods in an annuity.
Parameter4:The present value.
Parameter5:The future value.If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
Parameter6:The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

Example 1：IPmt(0.12,1,3,80000)
Example 2：IPmt(0.01,1,36,80000,50000)
Example 3：IPmt(0.01,1,36,80000,50000,1)
IS Returns whether two objects are the same
<Boolean>: <Member> IS <Member>
<Boolean>: <Level>IS <Level>
<Boolean>: <Hierarchy> IS <Hierarchy>
<Boolean>: <Dimension> IS <Dimension>
<Boolean>: <Tuple> IS <Tuple>

Example 1：[Customer].[Education].&[Bachelors] IS [Customer].[Education].&[Bachelors]
Example 2：[Date].[Due Fiscal Date-Hierarchy].[FiscalYear] IS [Date].[Due Fiscal Date-Hierarchy].[DateKey]
Example 3：[Date].[Due Fiscal Date-Hierarchy] IS [Date].[Due Calendar Date-Hierarchy]
Example 4：[Customer].[BirthDate] IS [Customer].[BirthDate]
Example 5：([Customer].[BirthDate],[Customer].[AddressLine]) IS ([Customer].[BirthDate],[Customer].[AddressLine])
IS EMPTY Determines if an expression evaluates to the empty cell value.
<Boolean>:<Member>IS EMPTY
<Boolean>: <Tuple>IS EMPTY

Example 1：[Customer].[Education].&[Primary School] Is Empty
Example 2：([Customer].[AddressLine],[Date].[CalendarQuarter]) IS EMPTY
IS NULL Returns whether an object is null. Currently always returns FALSE, returns TRUE only if the given Member Key cannot be cast to the type of Level Key.
<Boolean>: <Member> IS NULL
<Boolean>: <Level> IS NULL
<Boolean>: <Hierarchy> IS NULL
<Boolean>: <Dimension> IS NULL

Example 1：[Customer].[Education].&[Primary School] IS NULL
Example 2：[Date].[Due Calendar Date-Hierarchy].[CalendarYear] IS NULL
Example 3：[Customer].[person-Hierarchy] IS NULL
Example 4：[Customer].[Education].[Education] IS NULL
InStr Returns the position of the first occurrence of one string within another. Implements very basic form of InStr
<Numeric>: InStr(<String>, <String>)

InStr("kylingence","e")
InStrRev Returns the position of an occurrence of one string within another, from the end of string.
<Integer>: InStrRev(<String>, <String>, <Integer>, <Integer>)
Parameter1:The string expression to be searched.
Parameter2:The string expression that is to be searched for.
Parameter3:A numeric expression that sets the starting position for each search. If this value is omitted, the search begins at the first character position. If start is null, the function return value is undefined.
Parameter4:An integer value. This argument is always ignored. It is defined for compatibility with other Instr functions in other languages.

Example 1：InStrRev("kylingence","e")
Example 2：InStrRev("kylingence","e",2)
Example 3：InStrRev("kylingence","e",2,1)
Int Returns the integer portion of a number. If negative, returns the negative number less than or equal to the number.
<Integer>: Int(<Value>)

Int(1.3)
Intersect Returns the intersection of two input sets, optionally retaining duplicates.The optional ALL flag keeps duplicates. The two specified sets must have the same dimensions
<Set>: Intersect(<Set>, <Set>, <Symbol>)
<Set>: Intersect(<Set>, <Set>)
Symbol:ALL

Example 1：Intersect({[Customer].[Education].&[Bachelors],[Customer].[Education].&[High School]}, {[Customer].[Education].&[Bachelors]},All)
Example 2：Intersect({[Customer].[Education].&[Bachelors],[Customer].[Education].&[High School]}, {[Customer].[Education].&[Bachelors]})
IsDate Returns a Boolean value indicating whether an expression can be converted to a date.
<Boolean>: IsDate(<Value>)

IsDate(Date())
IsEmpty Determines if an expression evaluates to the empty cell value.
<Boolean>: IsEmpty(<String>)
<Boolean>: IsEmpty(<Numeric>)

Example 1：IsEmpty("")
Example 2：IsEmpty(2)
Item Returns a member from the tuple specified in <Tuple>. The member to be returned is specified by the zero-based position of the member in the set in <Index>.
<Member>: <Tuple>.Item(<Numeric>)

([Customer].[Education].&[Bachelors],[Date].[CalendarYear].&[2010]).Item(0)
Item Returns a tuple from the set specified in <Set>. The tuple to be returned is specified by the zero-based position of the tuple in the set in <Index>.
<Member>: <Set>.Item(<Numeric>)

{[Customer].[BirthDate].[BirthDate].Members}.Item(1)
Item Returns a tuple from the set specified in <Set>. The tuple to be returned is specified by the member name (or names) in <String>.
<Set>.Item(<String>[, <String>...])

{[Customer].[Education].[Education].Members}.Item("Bachelors")
LCase Returns a String that has been converted to lowercase.
<String>: LCase(<String>)

LCase("KYLINGENCE")
LTrim Returns a Variant (String) containing a copy of a specified string
without leading spaces.
<String>: LTrim(<String>)

LTrim(" kylin gence ")
Lag Returns a member further along the specified member's dimension.
<Member>: <Member>.Lag(<Numeric>)

[Customer].[Education].[High School].Lag(1)
LastChild Returns the last child of a member.
<Member>: <Member>.LastChild

[Date].[Due Calendar Date-Hierarchy].[CalendarYear].&[2013].LastChild
LastSibling Returns the last child of the parent of a member.
<Member>: <Member>.LastSibling

[Date].[CalendarYear].&[2010].LastSibling
Lead Returns a member further along the specified member's dimension.
<Member>: <Member>.Lead(<Numeric Expression>)

[Customer].[Education].&[High School].Lead(1)
Left Returns a specified number of characters from the left side of a string.
<String>: Left(<String>, <Integer>)

Left("kylingence",2)
Len Returns the number of characters in a string.
<Numeric>: Len(<String>)

Len("kylingence")
Level Returns a member's level.
<Level>: <Member>.Level

[Date].[Due Fiscal Date-Hierarchy].[FiscalYear].&[2010].Level
Levels Returns the level whose position in a hierarchy is specified by a numeric expression.
<Level>: <Hierarchy>.Levels(<Numeric Expression>)

[Date].[Due Fiscal Date-Hierarchy].levels(1)
Levels Returns the level whose name is specified by a string expression.
<Level>: <Hierarchy>.Levels(<String>)
<Level>: Levels(<String>)

Example 1：[Date].[Due Fiscal Date-Hierarchy].Levels("DateKey")
Example 2：Levels('[Customer].[person-Hierarchy].[Education]')
LinRegIntercept Calculates the linear regression of a set and returns the value of b in the regression line y = ax + b.
<Numeric>: LinRegIntercept(<Set>, <Numeric>)
<Numeric>: LinRegIntercept(<Set>, <Numeric>, <Numeric>)
Parameter1:A valid Multidimensional Expressions (MDX) expression that returns a set.
Parameter2:A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number that represents values for the y-axis.
Parameter3:A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number that represents values for the x-axis.

LinRegIntercept(LastPeriods(3,[Date].[CalendarYear].&[2014]),[Measures].[InternetSales Count],[Measures].[Reseller Count])
LinRegPoint Calculates the linear regression of a set and returns the value of y in the regression line y = ax + b.
<Numeric>: LinRegPoint(<Numeric>, <Set>, <Numeric>)
<Numeric>: LinRegPoint(<Numeric>, <Set>, <Numeric>, <Numeric>)
Parameter1:A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number that represents values for the slicer axis.
Parameter2:A valid Multidimensional Expressions (MDX) expression that returns a set.
Parameter3:A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number that represents values for the y-axis.
Parameter4:A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number that represents values for the x-axis.

LinRegPoint([Measures].[Count],LastPeriods(3,[Date].[CalendarYear].&[2014]),[Measures].[InternetSales Count],[Measures].[Reseller Count])
LinRegR2 Calculates the linear regression of a set and returns R2 (the coefficient of determination).
<Numeric>: LinRegR2(<Set>, <Numeric>)
<Numeric>: LinRegR2(<Set>, <Numeric>, <Numeric>)
Parameter1:A valid Multidimensional Expressions (MDX) expression that returns a set.
Parameter2:A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number that represents values for the y-axis.
Parameter3:A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number that represents values for the x-axis.

LinRegR2(LastPeriods(3,[Date].[CalendarYear].&[2014]),
[Measures].[InternetSales Count],[Measures].[Reseller Count])
LinRegSlope Calculates the linear regression of a set and returns the value of a in the regression line y = ax + b.
<Numeric>: LinRegSlope(<Set>, <Numeric>)
<Numeric Expression>: LinRegSlope(<Set>, <Numeric>, <Numeric>)
Parameter1:A valid Multidimensional Expressions (MDX) expression that returns a set.
Parameter2:A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number that represents values for the y-axis.
Parameter3:A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number that represents values for the x-axis.

LinRegSlope(LastPeriods(3,[Date].[CalendarYear].&[2014]),[Measures].[InternetSales Count],[Measures].[Reseller Count])
LinRegVariance Calculates the linear regression of a set and returns the variance associated with the regression line y = ax + b.
<Numeric>: LinRegVariance(<Set>, <Numeric>)
<Numeric>: LinRegVariance(<Set>, <Numeric>, <Numeric>)
Parameter1:A valid Multidimensional Expressions (MDX) expression that returns a set.
Parameter2:A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number that represents values for the y-axis.
Parameter3:A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number that represents values for the x-axis.

LinRegVariance(LastPeriods(3,[Date].[CalendarYear].&[2014]),[Measures].[InternetSales Count],[Measures].[Reseller Count])
Log Returns a Double specifying the natural logarithm of a number.
<Numeric>: Log(<Numeric>)

Log(Exp(2))
Log10 Returns the base-10 logarithm of a number.
<Numeric>: Log10(<Numeric>)

Log10(100)
Max Returns the maximum value of a numeric expression evaluated over a set.
<Numeric>: Max(<Set>)
<Numeric>: Max(<Set>, <Numeric>)

Example 1：max({[Date].[CalendarYear].[CalendarYear].Members})
Example 2：max({[Date].[CalendarYear].[CalendarYear].Members},[Measures].[Count])
Median Returns the median value of a numeric expression evaluated over a set.
<Numeric> Median(<Set>)
<Numeric>: Median(<Set>, <Numeric>)

Example 1：Median({[Date].[CalendarYear].[CalendarYear].Members})
Example 2：Median({[Date].[CalendarYear].[CalendarYear].Members},[Measures].[Count])
Members Returns the set of members in an object.
<Set>: <Dimension>.Members
<Set>: <Hierarchy>.Members
<Set>: <Level>.Members


Example 1：[Date].[CalendarYear].[CalendarYear].Members
Example 2：[Customer].[person-Hierarchy].Members
Example 3：[Date].[Due Fiscal Date-Hierarchy].[FiscalYear].Members
Mid Returns a specified number of characters from a string.
<String>: Mid(<String>, <Integer>, <Integer>)
Parameter1:string expression from which characters are returned.
Parameter2:Character position in string at which the part to be taken begins. If start is greater than the number of characters in string, Mid returns a zero-length string ("").
Parameter3:Number of characters to return. If omitted or if there are fewer than length characters in the text (including the character at start, all characters from the start position to the end of the string are returned.

Example 1：Mid("kylingence",3)
Example 2：Mid("kylingence",2,4)
Min Returns the minimum value of a numeric expression evaluated over a set.
<Numeric>: Min(<Set>)
<Numeric>: Min(<Set>,<Numeric>)

min([Date].[CalendarYear].[CalendarYear].Members,[Measures].[Count])
Minute Returns a Variant (Integer) specifying a whole number between 0 and 59, inclusive, representing the minute of the hour.
<Integer>: Minute(<DateTime>)

Minute(CDate('2019-05-23 20:49:28'))
Month Returns a Variant (Integer) specifying a whole number between 1 and 12, inclusive, representing the month of the year.
<Integer>: Month(<DateTime>)

Month(CDate('2012-03-24 18:39:28'))
Mtd A shortcut function for the PeriodsToDate function that specifies the level to be Month.
<Set>: Mtd() <Set>: Mtd(<Member>)

MTD([Date].[Calendar-Hierarchy].currentmember)
NOT Returns the negation of a condition.
<Boolean>: NOT <Boolean>

NOT [Measures].[Count]>80000
Name Returns the name of an object.
<String>: <Dimension>.Name
<String>: <Hierarchy>.Name
<String>: <Level>.Name
<String>: <Member>.Name

Example 1：[Customer].[Education].[Education].Name
Example 2：[Customer].[person-Hierarchy].name
Example 3：[Date].[Due Fiscal Date-Hierarchy].[FiscalYear].name
Example 4：[Date].[Due Fiscal Date-Hierarchy].[FiscalYear].[2010].name
NextMember Returns the next member in the level that contains a specified member.
<Member>: <Member>.NextMember

[Customer].[Education].[High School].NextMember
NonEmptyCrossJoin Returns the cross product of two sets, excluding empty tuples and tuples without associated fact table data.
<Set>: NonEmptyCrossJoin(<Set>, <Set>)

NonEmptyCrossJoin([Customer].[LastName].[LastName].Members, [Customer].[GENDER].[GENDER].Members)
Now Returns a Variant (Date) specifying the current date and time according your computer's system date and time.
<DateTime>: Now()

Now()
OR Returns the disjunction of two conditions.
<Boolean>: <Boolean> OR <Boolean>

[Measures].[Count]>80000 OR [Measures].[SalesAmout]>30000000
Oct Returns a Variant (String) representing the octal value of a number.
<String>: Oct(<Value>)

Oct([Measures].[Count])
OpeningPeriod Returns the first descendant of a member at a level.
<Member>: OpeningPeriod()
<Member> OpeningPeriod(<Level>)
<Member>: OpeningPeriod(<Level>,<Member>)

Example 1：OpeningPeriod([Date].[Due Fiscal Date-Hierarchy].[FiscalYear])
Example 2：OpeningPeriod([Date].[Due Fiscal Date-Hierarchy].[FiscalYear],[Date].[Due Fiscal Date-Hierarchy].[FiscalYear].&[2010])
Example 3：[Date].[Due Fiscal Date-Hierarchy].[FiscalYear].openingperiod
Order Arranges members of a set, optionally preserving or breaking the hierarchy.If ASC or DESC is specified, the Order function first arranges the members according to their position in the hierarchy, and then orders each level. If BASC or BDESC is specified, the Order function arranges members in the set without regard to the hierarchy. In no flag is specified, ASC is the default.
<Set>: Order(<Set>, <Value>, <Symbol>)
<Set>: Order(<Set>, <Value>)


Example 1：Order([Date].[CalendarYear].[CalendarYear].members, [Measures].[Count], DESC)
Example 2：Order([Date].[Due Calendar Date-Hierarchy].[CalendarYear].members, [Measures].[Count])
Ordinal Returns the zero-based ordinal value associated with a level.
<Integer>: <Level>.Ordinal

[Date].[CalendarYear].[CalendarYear].Ordinal
PPmt Returns a Double specifying the principal payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.
<Numeric>: PPmt(<Numeric>, <Numeric>,<Numeric>, <Numeric>,
<Numeric>,<Boolean>)
Parameter1:The interest rate per period.
Parameter2:Specifies the period and must be in the range 1 to nper.
Parameter3:The total number of payment periods in an annuity.
Parameter4:The present value — the total amount that a series of future payments is worth now.

Parameter6:The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

Example 1：PPmt(0.1, 1, 30, 100000)
Example 2：PPmt(0.1, 15, 30, 100000, 0)
Example 3：PPmt(0.1, 1, 30, 100000, 0, CBool(0))
PV Returns a Double specifying the present value of an annuity based on periodic, fixed payments to be paid in the future and a fixed interest rate.
<Numeric>: PV(<Numeric>, <Numeric>, <Numeric>, <Numeric>,
<Boolean>)

PV(0,3,2,7,CBool(1))
ParallelPeriod Returns a member from a prior period in the same relative position as a specified member.
<Member>: ParallelPeriod()
<Member>: ParallelPeriod(<Level>)
<Member>: ParallelPeriod(<Level>, <Numeric>)
<Member>: ParallelPeriod(<Level>, <Numeric>,<Member>)

Example 1：ParallelPeriod([Date].[Calendar-Hierarchy].[CalendarYear])
Example 2：ParallelPeriod([Date].[Calendar-Hierarchy].[CalendarQuarter], 1)
Example 3：ParallelPeriod([Date].[Calendar-Hierarchy].[CalendarYear],1,[Date].[Calendar-Hierarchy].currentMember
Parameter Returns default value of parameter.
<String>: Parameter(<String>, <Symbol>, <String>, <String>)
<String>: Parameter(<String>, <Symbol>, <String>)
<Numeric>: Parameter(<String>, <Symbol>, <Numeric>, <String>)
<Numeric>: Parameter(<String>, <Symbol>, <Numeric>)
<Member>: Parameter(<String>, <Hierarchy>, <Member>, <String>)
<Member>: Parameter(<String>, <Hierarchy>,<Member>)
Parent Returns the parent of a member.
<Member>: <Member>.Parent

[Date].[Due Fiscal Date-Hierarchy].[FiscalYear].&[2013].&[1].&[1].Parent
Percentile Returns the value of the tuple that is at a given percentile of a set.
<Numeric>: Percentile(<Set>, <Numeric>, <Numeric>)
Parameter1:The array or range of data that defines relative standing.
Parameter2:The Value of the query.
Parameter3:The percentile value in the range 0..1, inclusive.

Percentile([Date].[CalendarQuarter].[CalendarQuarter].members, [Measures].[Count], 1)
PeriodsToDate Returns a set of periods (members) from a specified level starting with the first period and ending with a specified member.
<Set>: PeriodsToDate()
<Set>: PeriodsToDate(<Level>)
<Set>: PeriodsToDate(<Level>, <Member>)

Example 1：PeriodsToDate([Date].[Calendar-Hierarchy].[CalendarQuarter])
Example 2：PeriodsToDate([Date].[Calendar-Hierarchy].[CalendarYear],[Date].[Calendar-Hierarchy].[MONTHNUMBEROFYEAR].&[34])
Pi Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.
<Numeric>: Pi()

Pi()
Pmt Returns a Double specifying the payment for an annuity based on periodic, fixed payments and a fixed interest rate.
<Numeric>: Pmt(<Numeric>, <Numeric>,<Numeric>, <Numeric>, <Boolean>)
<Numeric>: Pmt(<Numeric>, <Numeric>,<Numeric>, <Numeric>, <Boolean>)
Parameter1:The interest rate for the loan.
Parameter2:The total number of payments for the loan.
Parameter3:The present value, or the total amount that a series of future payments is worth now; also known as the principal.
Parameter4:The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Parameter5:The number 0 (zero) or 1 and indicates when payments are due.

Pmt(0, 3, 2, 7, CBool(1))
Power Returns the result of a number raised to a power.
<Numeric>: Power(<Numeric>, <Numeric>)

Power(4, 0.5)
PrevMember Returns the previous member in the level that contains a specified member.
<Member>: <Member>.PrevMember

[Date].[CalendarYear].&[2012].PrevMember
Properties Returns the value of a member property.
<Member>.Properties(<String Expression>)

[Date].[CalendarYear].[CalendarYear].&[2007].Properties('Caption')
Qtd A shortcut function for the PeriodsToDate function that specifies the level to be Quarter.
<Set>: Qtd() <Set>: Qtd(<Member>)

QTD([Date].[Calendar-Hierarchy].currentmember)
RTrim Returns a Variant (String) containing a copy of a specified string without trailing spaces.
<String>: RTrim(<String>)

RTrim('abc ')
Radians Converts degrees to radians.
<Numeric>: Radians(<Numeric>)

Radians(180)
Rank Returns the one-based rank of a tuple in a set.
<Integer>: Rank(<Tuple>, <Set>)
<Integer>: Rank(<Tuple>, <Set>, <Numeric>)
<Integer>: Rank(<Member>, <Set>)
<Integer> Rank(<Member>, <Set>, <Numeric>)

Example 1：Rank(([Date].[CalendarYear].&[2007],[Customer].[LastName].&[Adams]), [Date].[CalendarYear].[CalendarYear].members* [Customer].[LastName].[LastName].members)
Example 2：Rank(([Date].[CalendarYear].&[2007],[Customer].[LastName].&[Adams]), [Date].[CalendarYear].[CalendarYear].members* [Customer].[LastName].[LastName].members, [Measures].[Count])
Example 3：Rank([Date].[CalendarYear].&[2007], [Date].[CalendarYear].[CalendarYear].members)
Example 4：Rank([Date].[CalendarYear].&[2007], [Date].[CalendarYear].[CalendarYear].members, [Measures].[Count])
Rate Returns a Double specifying the interest rate per period for an annuity.
<Numeric>: Rate(<Numeric>, <Numeric>, <Numeric>, <Numeric>,
<Boolean>, <Numeric>)
Parameter1:The total number of payment periods in an annuity.
Parameter2:The payment made each period and cannot change over the life of the annuity.
Parameter3:The present value — the total amount that a series of future payments is worth now.
Parameter4:The future value, or a cash balance you want to attain after the last payment is made.
Parameter5:The number 0 or 1 and indicates when payments are due.
Parameter6:Your guess for what the rate will be.

Example 1：Rate(12, 500, 3000)
Example 2：Rate(12, 500, 3000, 300000)
Example 3：Rate(12, 500, 3000, 300000, CBool(0))
Example 4：Rate(12, 500, 3000, 300000, CBool(0), 1)
Replace Returns a string in which a specified substring has been replaced with another substring a specified number of times.
<String>: Replace(<String>, <String>, <String>, <Integer>, <Integer>, <Integer>)
Parameter1:Meta String.
Parameter2:Searched substring.
Parameter3:Replaced substring.
Parameter4:The search start position, if omitted, it is assumed to be 1.
Parameter5:The number of substring replacements to be performed. If omitted, it is assumed to be -1, which means that all replacements are performed.
Parameter6:The comparison type when calculating substrings.

Replace('kylingence', gence', 'mdx', 1, 1, 1)
Right Returns a Variant (String) containing a specified number of characters from the right side of a string.
<String>: Right(<String>, <Integer>)

Right('kylingence', 2)
Round Returns a number rounded to a specified number of decimal places.
<Numeric>: Round(<Numeric>, <Integer>)
<Numeric>: Round(<Numeric>)

Example 1：Round(1.123)
Example 2：Round(1.123, 2)
SLN Returns a Double specifying the straight-line depreciation of an asset for a single period.
<Numeric>: SLN(<Numeric>, <Numeric>,<Numeric>)
Parameter1:The initial cost of the asset.
Parameter2:The value at the end of the depreciation (sometimes called the salvage value of the asset).
Parameter3:The number of periods over which the asset is depreciated (sometimes called the useful life of the asset).

SLN(100, 10, 5)
SYD Returns a Double specifying the sum-of-years' digits depreciation of an asset for a specified period.
<Numeric>: SYD(<Numeric>, <Numeric>, <Numeric>, <Numeric>)
Parameter1:The initial cost of the asset.
Parameter2:The value at the end of the depreciation (sometimes called the salvage value of the asset).
Parameter3:The number of periods over which the asset is depreciated (sometimes called the useful life of the asset).
Parameter4:The period and must use the same units as life.

SYD(1000, 100, 5, 5)
Second Returns a Variant (Integer) specifying a whole number between 0 and 59, inclusive, representing the second of the minute.
<Integer>: Second(<DateTime>)

Second(CDate('2013-02-23 16:39:28'))
SetToStr Constructs a string from a set.
<String>: SetToStr(<Set>)

SetToStr([Date].[CalendarYear].Children)
Sgn Returns a Variant (Integer) indicating the sign of a number.
<Integer>: Sgn(<Numeric>)

Sgn(3.11111)
Siblings Returns the siblings of a specified member, including the member itself.
<Member>.Siblings

[Date].[CalendarYear].[CalendarYear].&[2007].Siblings
Sin Returns a Double specifying the sine of an angle.
<Numeric>: Sin(<Numeric Expression>)

Sin(60*Pi()/180)
Sinh Returns the hyperbolic sine of a number.
<Numeric>: Sinh(<Numeric Expression>)

Sinh(-2)
Space Returns a Variant (String) consisting of the specified number of spaces.
<String>: Space(<Integer>)

"kylingence"||Space(3)||"MDX"
Sqr Returns a Double specifying the square root of a number.
<Numeric>: Sqr(<Numeric Expression>)

Sqr(4)
SqrtPi Returns the square root of (number * pi).
<Numeric>: SqrtPi(<Numeric Expression>)

SqrtPi(4)
Stddev Alias for Stdev.
<Numeric>: Stddev(<Set>)
<Numeric Expression> Stddev(<Set>, <Numeric Expression>)

Example 1：StdDev({[Date].[CalendarYear].&[2010],[Date].[CalendarYear].&[2011],[Date].[CalendarYear].&[2012]})
Example 2：StdDev({[Date].[CalendarYear].&[2010],[Date].[CalendarYear].&[2011],[Date].[CalendarYear].&[2012]},[Measures].[Count])
StddevP Alias for StdevP.
<Numeric>: StddevP(<Set>)
<Numeric Expression>StddevP(<Set>,<Numeric Expression>)

Example 1：StddevP({[Date].[CalendarYear].&[2010],[Date].[CalendarYear].&[2011],[Date].[CalendarYear].&[2012]})
Example 2：StddevP({[Date].[CalendarYear].&[2010],[Date].[CalendarYear].&[2011],[Date].[CalendarYear].&[2012]}
,[Measures].[Count])
Stdev Returns the standard deviation of a numeric expression evaluated over a set (unbiased).
<Numeric>: Stdev(<Set>)
<Numeric Expression> Stdev(<Set>, <Numeric Expression>)

Example 1：Stdev({[Date].[CalendarYear].&[2010],[Date].[CalendarYear].&[2011],[Date].[CalendarYear].&[2012]})
Example 2：Stdev({[Date].[CalendarYear].&[2010],[Date].[CalendarYear].&[2011],[Date].[CalendarYear].&[2012]},[Measures].[Count])
StdevP Returns the standard deviation of a numeric expression evaluated over a set (biased).
<Numeric>: StdevP(<Set>)
<Numeric Expression>StdevP(<Set>, <Numeric Expression>)

Example 1：StdevP({[Date].[CalendarYear].&[2010],[Date].[CalendarYear].&[2011],[Date].[CalendarYear].&[2012]})
Example 2：StdevP({[Date].[CalendarYear].&[2010],[Date].[CalendarYear].&[2011],[Date].[CalendarYear].&[2012]},[Measures].[Count])
Str Returns a Variant (String) representation of a number.
<String>: Str(<Value>)

Str(34)
StrComp Returns a Variant (Integer) indicating the result of a string comparison.
<Integer>: StrComp(<String>, <String>, <Integer>)
Parameter3:Specifies the type of string comparison. If the compare argument is Null, an error occurs. If compare is omitted, the Option Compare setting determines the type of comparison.

Example 1：StrComp("abcd","ABCD")
Example 2：StrComp("abcd","ABCD",0)StrComp("abcde","ABCD",1)
StrReverse Returns a string in which the character order of a specified string
is reversed.
<String>: StrReverse(<String>)

StrReverse("abcde")
StrToMember Returns a member from a unique name String in MDX format.
<Member>: StrToMember(<String>)

StrToMember('[Customer].[Education].&[Bachelors]')
StrToSet Constructs a set from a string expression.
<Set>: StrToSet(<String>[, <Dimension>...])

StrToSet("[Product].[Color].&[Red],[Product].[Color].&[Silver]"
,[Product].[Color])
StrToTuple Constructs a tuple from a string.
<Tuple>: StrToTuple(<String>)

StrToTuple("([Product].[Color].&[Red],[Product].[Class].&[H ])",[Product].[Color],[Product].[Class])
String
<String>: String(<Integer>, <String>)

String(3,"h")
Subset Returns a subset of elements from a set.
<Set>: Subset(<Set>, <Numeric Expression>)
<Set> Subset(<Set>, <Numeric Expression>,
<Numeric Expression>)
Parameter1:A valid numeric expression that specifies the position of the first tuple to be returned.
Parameter2:A valid numeric expression that specifies the number of tuples to be returned.

Example 1：SubSet({[Date].[CalendarYear].&[2010],[Date].[CalendarYear].&[2011],[Date].[CalendarYear].&[2012]},2)
Example 2：SubSet({[Date].[CalendarYear].&[2010], [Date].[CalendarYear].&[2011], [Date].[CalendarYear].&[2012]},1,3)
Sum Returns the sum of a numeric expression evaluated over a set.
<Numeric>: Sum(<Set>)
<Numeric Expression> Sum(<Set>, <Numeric Expression>)

Sum([Date].[DateKey].CurrentMember,[Measures].[Count])
Tail Returns a subset from the end of a set.
<Set>: Tail(<Set>)
<Set>: Tail(<Set>, <Numeric Expression>)

Example 1：Tail({[Date].[CalendarYear].&[2010], [Date].[CalendarYear].&[2011], [Date].[CalendarYear].&[2012], [Date].[CalendarYear].&[2013]})
Example 2：Tail({[Date].[CalendarYear].&[2010], [Date].[CalendarYear].&[2011], [Date].[CalendarYear].&[2012], [Date].[CalendarYear].&[2013]},2)
Tan Returns a Double specifying the tangent of an angle.
<Numeric>: Tan(<Numeric Expression>)

Tan(45*Pi()/180)
Tanh Returns the hyperbolic tangent of a number.
<Numeric>: Tanh(<Numeric Expression>)

Tanh(-2)
ThirdQ Returns the 3rd quartile value of a numeric expression evaluated over a set.
<Numeric>: ThirdQ(<Set>)
<Numeric Expression> ThirdQ(<Set>, <Numeric Expression>)

Example 1：ThirdQ({[Date].[CalendarYear].&[2010],[Date].[CalendarYear].&[2011],[Date].[CalendarYear].&[2012]})
Example 2：ThirdQ({[Date].[CalendarYear].&[2010], [Date].[CalendarYear].&[2011], [Date].[CalendarYear].&[2012], [Date].[CalendarYear].&[2013] },[Measures].[Count])
Time Returns a Variant (Date) indicating the current system time.
<DateTime>: Time()

Time()
TimeSerial Returns a Variant (Date) containing the time for a specific hour,minute, and second.
<DateTime>: TimeSerial(<Integer>, <Integer>, <Integer>)
Parameter1:Hour.
Parameter2:Minute
Parameter3:Second

TimeSerial(16,34,17)
TimeValue Returns a Variant (Date) containing the time.
<DateTime>: TimeValue(<DateTime>)

TimeValue(CDate('2020-09-02 18:20'))
Timer Returns a Single representing the number of seconds elapsed since midnight.
<Numeric>: Timer()

Timer()
ToggleDrillState The ToggleDrillState function toggles the drill state of each member of the second set that is present in the first set. The first set can contain tuples with any dimensionality, but the second set must contain members of a single dimension. The ToggleDrillState function is a combination of the DrillupMember and DrilldownMember functions. If the member, m, of the second set is present in the first set, and that member is drilled down (that is, has a descendant immediately following it), then DrillupMember(Set_Expression1, {m}) is applied to the member or tuple in the first set.
<Set>: ToggleDrillState(<Set>, <Set>)
<Set> ToggleDrillState(<Set>, <Set>, <Symbol>)

Example 1：ToggleDrillState({[Customer].[Education].[Education].Members},{[Customer].[Education].&[Bachelors],[Customer].[Education].&[High School]})
TopCount Returns a specified number of items from the top of a set, optionally ordering the set first.
<Set>: TopCount(<Set>, <Numeric Expression>, <Numeric Expression>)
<Set>: TopCount(<Set>, <Numeric Expression>)

Example 1：TopCount({[Customer].[Education].[Education].Members},3,[Measures].[Count])
Example 2：TopCount({[Customer].[Education].[Education].Members},3)
TopPercent Sorts a set and returns the top N elements whose cumulative total is at least a specified percentage.
<Set>: TopPercent(<Set>, <Numeric Expression>, <Numeric Expression>)

TopPercent({[Product].[Color].[Color].Members},60,[Measures].[Count])
TopSum Sorts a set and returns the top N elements whose cumulative total is at least a specified value.
<Set>: TopSum(<Set>, <Numeric Expression>, <Numeric Expression>)

TopSum({[Customer].[Education].[Education].Members},30000,[Measures].[Count])
Trim Returns a Variant (String) containing a copy of a specified string without leading and trailing spaces.
<String>: Trim(<String>)

Trim(" delete space ")
TupleToStr Constructs a string from a tuple.
<String>: TupleToStr(<Tuple>)

TupleToStr(([Customer].[Education].&[Bachelors],[Date].[CalendarYear].&[2011]))
TypeName Returns a String that provides information about a variable.
<String>: TypeName(<Value>)

TypeName(2)
UCase Returns a string that has been converted to uppercase.
<String>: UCase(<String>)

ucase("Kylingence")
Union Returns the union of two sets, optionally retaining duplicates,using the ALL flag keeps duplicates in the joined set.
<Set>: Union(<Set>, <Set>)
<Set>: Union(<Set>, <Set>, <Symbol>)
Symbol:All

Example 1：Union({[Date].[CalendarYear].&[2010],[Date].[CalendarYear].&[2013]},{[Date].[CalendarYear].&[2010],[Date].[CalendarYear].&[2012]})
Example 2：Union({[Date].[CalendarYear].&[2010],[Date].[CalendarYear].&[2013]},{[Date].[CalendarYear].&[2010],[Date].[CalendarYear].&[2012]},All)
UniqueName Returns the unique name.
<String>: <Dimension>.UniqueName
<String>: <Hierarchy>.UniqueName
<String>: <Level>.UniqueName
<String>: <Member>.UniqueName

Example 1：[Customer].[Education].[Education].UniqueName
Example 2：[Customer].[person-Hierarchy].UniqueName
Example 3：[Customer].[person-Hierarchy].[Education].uniquename
Example 4：[Customer].[Education].&[Bachelors].UniqueName
Unorder Removes any enforced ordering from a specified set.
<Set>: Unorder(<Set>)

Unorder(order([Customer].[Education].[Education].Members,[Measures].[Count]))
Val Returns the numbers contained in a string as a numeric value of appropriate type.
<Numeric>: Val(<String>)

Val("34")
ValidMeasure Returns a valid measure in a virtual cube by forcing inapplicable dimensions to their top level.
<Numeric>: ValidMeasure(<Tuple>)

ValidMeasure(([Customer].[Education].&[Bachelors],[Date].[CalendarYear].&[2012]))
Value Returns the value of a measure.
<Member>.Value

[Date].[CalendarYear].&[2010].Value
Var Returns the variance of a numeric expression evaluated over a set (unbiased).
<Numeric>: Var(<Set>)
<Numeric>: Var(<Set>, <Numeric>)

Example 1：Var({[Date].[CalendarYear].&[2010], [Date].[CalendarYear].&[2011], [Date].[CalendarYear].&[2012] })
Example 2：Var({[Date].[CalendarYear].&[2010], [Date].[CalendarYear].&[2011], [Date].[CalendarYear].&[2012] },2)
VarP Returns the variance of a numeric expression evaluated over a set (biased).
<Numeric>: VarP(<Set>)
<Numeric>: VarP(<Set>, <Numeric Expression>)

Example 1：VarP({[Date].[CalendarYear].&[2010], [Date].[CalendarYear].&[2011], [Date].[CalendarYear].&[2012] })
Example 2：VarP({[Date].[CalendarYear].&[2010], [Date].[CalendarYear].&[2011], [Date].[CalendarYear].&[2012] },2)
Variance Alias for Var.
<Numeric>: Variance(<Set>)
<Numeric>: Variance(<Set>, <Numeric Expression>)

Example 1：Variance({[Date].[CalendarYear].&[2010], [Date].[CalendarYear].&[2011], [Date].[CalendarYear].&[2012] })
Example 2：Variance({[Date].[CalendarYear].&[2010], [Date].[CalendarYear].&[2011], [Date].[CalendarYear].&[2012] },3)
VarianceP Alias for VarP.
<Numeric>: VarianceP(<Set>)
<Numeric>: VarianceP(<Set>, <Numeric Expression>)

Example 1：VarianceP({[Date].[CalendarYear].&[2010], [Date].[CalendarYear].&[2011], [Date].[CalendarYear].&[2012] })
Example 2：VarianceP({[Date].[CalendarYear].&[2010], [Date].[CalendarYear].&[2011], [Date].[CalendarYear].&[2012] },3)
VisualTotals Dynamically totals child members specified in a set using a pattern for the total label in the result set.
<Set>: VisualTotals(<Set>)
<Set>: VisualTotals(<Set>, <String>)

Example 1：VisualTotals({[Customer].[Education].&[Bachelors],[Customer].[Education].&[High School], [Customer].[Education].&[Partial High School]})
Example 2：VisualTotals({[Customer].[Education].&[Bachelors],[Customer].[Education].&[High School], [Customer].[Education].&[Partial High School]},'* -Visualtotals')
Weekday Returns a Variant (Integer) containing a whole number representing the day of the week.
<Integer>: Weekday(<DateTime>, <Integer>)

Example 1：Weekday(Date())
Example 2：Weekday(Date(),3)
WeekdayName Returns a string indicating the specified day of the week.
<String>: WeekdayName(<Integer>, <Logical Expression>,
<Integer>)

WeekdayName(3,[Measures].[Count]<80000,2)
Wtd A shortcut function for the PeriodsToDate function that specifies the level to be Week.
<Set>: Wtd()
<Set>: Wtd(<Member>)

WTD([Date].[Calendar-Hierarchy].currentmember)
XOR Returns whether two conditions are mutually exclusive.
<Boolean>: <Boolean> XOR <Boolean>

[Measures].[Count]>80000 Xor [Measures].[SalesAmout]<1000
Year Returns a Variant (Integer) containing a whole number representing the year.
<Integer>: Year(<DateTime>)

Year(CDate("2020-09-03"))
Ytd A shortcut function for the PeriodsToDate function that specifies the level to be Year.
<Set>: Ytd()
<Set>: Ytd(<Member>)

YTD([Date].[Calendar-Hierarchy].currentmember)
_CaseMatch Evaluates various expressions, and returns the corresponding expression for the first which matches a particular value.。
Case <Expression> When <Boolean> Then <Expression> [...] [Else <Expression>] End

CASE sum([Date].[DateKey].CurrentMember,[Measures].[Count])
WHEN 0 THEN 'NONE'
WHEN 1 THEN 'SMALL'
ELSE 'LARGE'
END
_CaseTest Evaluates various conditions, and returns the corresponding expression for the first which evaluates to true.
Case When <Boolean> Then <Expression> [...] [Else <Expression>] End

CASE WHEN [Measures].[Count]<80000
THEN 'TRUE'
ELSE 'VERY LARGE'
END
|| Concatenates two strings.
<String>: <String>||<String>

'kylin'||'gence'
Powered by Gitbook.            Last Modified： 2022-03-20 16:37:32

results matching ""

    No results matching ""