MDX FirstSibling Function

The MDX FirstSibling function will return the First Child member that belongs to the parent of a specified member. For example, If you know a single customer name and you want to find the Sales of a first customer, then you can use this MDX FirstSibling function.

MDX FirstSibling Function Syntax

The basic syntax of the FirstSibling Function in Multidimensional Expression is as shown below:

Member_Expression.FIRSTSIBLING

Member_Expression: Any Multidimensional Expression that returns valid Member.

For this FirstSibling function in MDX query, we are going to use the below show data. The following screenshot shows the Countries inside the Geography

MDX FIRSTSIBLING FUNCTION

The following screenshot shows the [State – Provinces] inside the France Country

MDX FIRSTSIBLING FUNCTION 1

MDX FirstSibling Function Example

In this example, we are going to find the First Children present in the Countries list.

SELECT 
  [Measures].[Reseller Sales Amount] ON COLUMNS,
  [Geography].[Geography].[Country].[France].FIRSTSIBLING ON ROWS
FROM [Adventure Works];
MDX FIRSTSIBLING FUNCTION 1

In the above MDX Query, We used [Reseller Sales amount] on the columns

[Measures].[Reseller Sales Amount] ON COLUMNS

The below line of code will check for the France parent (Which is All member), and then finds the first child member of the Country.

[Geography].[Geography].[Country].[France].FIRSTSIBLING

MDX FirstSibling Function Example 2

If we know the Loiret is one of the states in France. And we intend to find the first state present in France. Then we can use this MDX FirstSibling function. In this example, we are going to find the First Children present in the State Province list and calculate the Reseller Sales Amount of that.

SELECT 
 [Measures].[Reseller Sales Amount] ON COLUMNS,
 [Geography].[Geography].[State-Province].[Loiret].FIRSTSIBLING ON ROWS
FROM [Adventure Works];
MDX FIRSTSIBLING FUNCTION 2

In the above MDX Query, We used [Reseller Sales amount] on the columns

[Measures].[Reseller Sales Amount] ON COLUMNS

The below firstsibling line of code will check for the Loiret parent (Which is France) and then finds the first child member of the France Country.

[Geography].[Geography].[Country].[France].FIRSTSIBLING

For Charente-Maritime State, there are no sales at all. So, it is displaying Null results.

MDX FirstSibling Function Alternative

In this example, we are going to use the FirstSibling function alternative to achieve the same result. Please refer to the MDX Parent Function to understand the Parent function and refer to MDX FirstChild function to understand the FirstChild function.

SELECT 
 [Measures].[Reseller Sales Amount] ON COLUMNS,
 [Geography].[Geography].[State-Province].[Loiret].PARENT.FIRSTCHILD ON ROWS
FROM [Adventure Works];
MDX FIRSTSIBLING FUNCTION 3