CUBERANKEDMEMBER function
the nth, or ranked, member in a set. Use to return one or more elements in a set, such as the top sales performer or top 10 students.
Cube · Guided Excel example
Where can you use it?
Data model and business intelligence teams. When checking measures, members and sets in a cube report, adapt the model and connection names to your workbook.
Scenario in the workbook
Retrieve a member at a one-based position in the set. Replace column A with the real cube connection name. Match the sample MDX identifiers to your data model.
A worked example from the workbook
| Row | A | B | C |
|---|---|---|---|
| 6 | Connection | Member or set | Selection |
| 7 | SalesCube | {[Region].[North],[Region].[South],[Region].[East]} | 1 |
| 8 | SalesCube | {[Region].[North],[Region].[South],[Region].[East]} | 2 |
| 9 | SalesCube | {[Region].[North],[Region].[South],[Region].[East]} | 3 |
=CUBERANKEDMEMBER(A7,CUBESET(A7,B7),C7)Illustrative outputDepends on model
See the inputs and instructions for all 3 examplesWorked examples
Retrieve a member at a one-based position in the set. Replace column A with the real cube connection name. Match the sample MDX identifiers to your data model.
Column F provides expressions as text. Satisfy the stated requirements, then paste into an empty Excel cell and compare with column G. The result was not verified in desktop Excel here.
| Row | A | B | C |
|---|---|---|---|
| 6 | Connection | Member or set | Selection |
| 7 | SalesCube | {[Region].[North],[Region].[South],[Region].[East]} | 1 |
| 8 | SalesCube | {[Region].[North],[Region].[South],[Region].[East]} | 2 |
| 9 | SalesCube | {[Region].[North],[Region].[South],[Region].[East]} | 3 |
| Trial | Expression | Sample output |
|---|---|---|
| Record 1 | =CUBERANKEDMEMBER(A7,CUBESET(A7,B7),C7) |
Depends on model |
| Record 2 | =CUBERANKEDMEMBER(A8,CUBESET(A8,B8),C8) |
Depends on model |
| Record 3 | =CUBERANKEDMEMBER(A9,CUBESET(A9,B9),C9) |
Depends on model |