MEMBERINDEX (dimension, member)

Description:

This function returns the index of the member given as the member parameter in the function. The member may be an alias name. Members are numbered 1...n.

Syntax: MEMBERINDEX (Dimension, Member)

• Dimension : is the name of the dimension from which you want to access members. The dimension may belong to a cube or it may independently reside in the database. Its members are all accessed by a reference.
• Member : is the name/reference of the member in a dimension. This value should state the member name exactly as it is spelled in the source dimension. As the members in a dimension are numbered beginning from 1 till "n" in the order they are created or re-arranged, thereby the index number of the member you have given as an input to the function will be returned accordingly.

Remarks

• Both arguments are compulsory.
• If you specify a number instead of a text value for any of the parameters then the function would return an error which is- #VALUE.

Example

The MEMBERINDEX function can be used in any database where you want to access the index number/numbers that corresponds to the given member in a given dimension and this "memberindex" can either used as a reference in a formula or it is used to populate a fact data value into another member. This function returns the name of the member corresponding to the index number as the result of the function.

=MEMBERINDEX ("Time", "Period_9")	- returns the index of the member named "Period_9" in the dimension named "Time" from the database where the formula is written. This function returns the value 9 which is the index of the member "Period_9".
=MEMBERINDEX ("Version", "Budget")	- returns the index of the member "Budget" which belongs to the "Version" dimension. The function returns the value 2 which is the index of the member referenced above.

 

Note: A number supplied within double quotes is taken as a string parameter.

 

Let us take the example of a database in PowerOLAP where we have dimensions and member names whose index is to be accessed for referencing in a formula or then the index is to be accessed to assign that value as fact data to another member. We shall use this example in this case to populate a value into a cube that uses a member name from a specified dimension at a given index as a fact value to populate its own members within its own dimension.

The index of a member name from one dimension becomes the fact data for another member in another dimension and the value is pulled from one through to another, with the use of the "MEMBERINDEX" function.



A numeric value signifying the index number is returned as the result for the MEMBERINDEX function. The formula above writes values into the "Date Data" cube to the "Date Measure" dimension into the member named "Year" by creating the appropriate value for this member based upon the parameters that are given as input to this function. The input parameters here are the dimension name which is "Date Year" dimension and the member name which is the accessed through the current function from the "Date Year" dimension. The index of the member referenced in the current function, is returned by this function and this number is populated into the "Year" member of the "Date Measure" dimension as the formula instructs.