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 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 a valid Member.
For this FirstSibling function in the MDX query, we are going to use the below shown data. The following screenshot shows the Countries inside the Geography.
The following screenshot shows the [State – Provinces] inside the France Country
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];
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];
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 Parent Function to understand the Parent function and refer to the 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];