Definition
The SIBLINGS MDX function in Planning Analytics allows you to return the siblings of a specified member including the member itself. This TM1 MDX function can be applied with other MDX functions like TM1FILTERBYLEVEL, TM1SUBSETALL. These complex MDX can be run against the server or when creating/editing dynamic subsets.
Syntax of SIBLINGS MDX
Here is the syntax for the SIBLINGS function :
<member>.SiblingsWhere:
- <member>: an existing element in a dimension.
- Siblings: is the MDX function.
Example of the SIBLINGS MDX Function
Here we will use a Product dimension to illustrate the functionality of the SIBLINGS MDX function. The Product dimension structure is shown below.
Three children exist for Cardio (20000) within the Product dimension. Using the following MDX on Bikes (SKU 21000) will return Bikes, Cross Trainers and Treadmills.
[Product].[Product].[21000].SIBLINGSHere is the results:
Complex MDX using SIBLINGS
In the screenshot below, the Product dimension is in rows and Unit price, Unit Sold and Unit cost measures are in columns.
![displays the result after applying the MDX TM1FILTERBYLEVEL(TM1SUBSETALL([Product].[Product]) , 0) and click apply to see the data with in the cube.](https://exploringtm1.com/wp-content/uploads/2024/07/image-3.png.avif)
Now let’s apply the SIBLINGS MDX function in a complex MDX expression.
The MDX below is to find the siblings of Bikes (21000), for which the Actual Gross profit is less than $5000 and present the two with the least Gross Profit.
TAIL(FILTER([Product].[Product].[21000].SIBLINGS , [Gross Profit].([Version].[Version].[Actual]) < 5000) , 2)Here is the results:
The MDX expression above is complex because we apply a FILTER MDX function on top of the SIBLINGS MDX function and then we use a TAIL function over the top of the Filter function.
The data is now displayed for the two elements resulted from the MDX applied on the Product dimension.
Do you need Help with MDX?
If you’d like some help with MDX in TM1/Planning Analytics – be it a simple tip to correct some code, or some assistance building new reports or PAW dashboards, please get in touch. We’d be delighted to help.
MDX Knowledge
We trust above content would have helped you gain some knowledge. To continue learning MDX check out our series of posts all on MDX. In addition to these, for MDX related information you may visit this list on the IBM website .
