MDX Expression needed

[SBK88]

I have 5 Elements A, B, C, D, E ( all are of level 4-5..... they have hierarchy of their own).... Dimension Name : PRODUCT
C is Child of B

I am looking for an MDX Expression to display A, B, C and all only Leaf level Elements of C

Need Help

[Wim Gielis]

Code: Select all

{[PRODUCT].[A]} + {[PRODUCT].[B]} + {[PRODUCT].[C]} + {TM1FilterByLevel( Descendants([PRODUCT].[C]), 0)}

[gtonkin]

Apologies OP for high-jacking the thread to some degree but was intrigued with Wim's notation using the plus sign to "add" elements to the set.
I have always just done:

Code: Select all

{[PRODUCT].[A], [PRODUCT].[B], [PRODUCT].[C], TM1FilterByLevel( Descendants([PRODUCT].[C]), 0)}

but like the plus signs for readability-Thanks Wim.

[lotsaram]

Apologies OP for higj-jacking the thread to some degree but was intrigued with Wim's notation using the plus sign to "add" elements to the set.
I have always just done:

Code: Select all

{[PRODUCT].[A], [PRODUCT].[B], [PRODUCT].[C], TM1FilterByLevel( Descendants([PRODUCT].[C]), 0)}

but like the plus signs for readability-Thanks Wim.

In MDX "+" is shorthand for UNION
e.g.
{set1} + {set2}
=
{UNION( {set1}, {set2} )}

A union will automatically remove duplicates from the sets, whereas the comma operator simply concatenates the sets together (without removing duplicates). You can show this pretty simply by comparing
{ {set1}, {set1} }
vs.
{ {set1} + {set1} }

[gtonkin]

Thanks for the clarification Lotsa-will keep that in mind.

[Wim Gielis]

Indeed, we have UNION( ), we have a comma and we have the + operator.
They differ when it comes to duplicate elements in the selection.

[SBK88]

Yes UNION worked for me......
Created 2 separate expressions then used UNION and it worked.

Thanks to all of u

[yyi]

Code: Select all

{[PRODUCT].[A]} + {[PRODUCT].[B]} + {[PRODUCT].[C]} + {TM1FilterByLevel( Descendants([PRODUCT].[B^C]), 0)}

just while I was refreshing the browser anxiously to see if anyone answered the question in my post (does anyone else do this I wonder..); I thought I'd add an idea to this thread - if there are any alternate hierarchy used in product dim, then it may be necessary to explicitly state the rollup name where the descendants come from [B1 or B2 being Parent1 or Parent2].