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
MDX Expression needed
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: MDX Expression needed
Code: Select all
{[PRODUCT].[A]} + {[PRODUCT].[B]} + {[PRODUCT].[C]} + {TM1FilterByLevel( Descendants([PRODUCT].[C]), 0)}
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- gtonkin
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: MDX Expression needed
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:
but like the plus signs for readability-Thanks Wim.
I have always just done:
Code: Select all
{[PRODUCT].[A], [PRODUCT].[B], [PRODUCT].[C], TM1FilterByLevel( Descendants([PRODUCT].[C]), 0)}
Last edited by gtonkin on Fri May 12, 2017 4:23 pm, edited 2 times in total.
-
- MVP
- Posts: 3652
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: MDX Expression needed
In MDX "+" is shorthand for UNIONgtonkin wrote: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:but like the plus signs for readability-Thanks Wim.Code: Select all
{[PRODUCT].[A], [PRODUCT].[B], [PRODUCT].[C], TM1FilterByLevel( Descendants([PRODUCT].[C]), 0)}
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} }
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
- gtonkin
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: MDX Expression needed
Thanks for the clarification Lotsa-will keep that in mind.
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: MDX Expression needed
Indeed, we have UNION( ), we have a comma and we have the + operator.
They differ when it comes to duplicate elements in the selection.
They differ when it comes to duplicate elements in the selection.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 45
- Joined: Fri Apr 17, 2015 5:55 am
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2013
Re: MDX Expression needed
Yes UNION worked for me......
Created 2 separate expressions then used UNION and it worked.
Thanks to all of u
Created 2 separate expressions then used UNION and it worked.
Thanks to all of u
Re: MDX Expression needed
Code: Select all
{[PRODUCT].[A]} + {[PRODUCT].[B]} + {[PRODUCT].[C]} + {TM1FilterByLevel( Descendants([PRODUCT].[B^C]), 0)}
Yeon