MDX Expression needed

Post Reply
SBK88
Posts: 38
Joined: Fri Apr 17, 2015 5:55 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2013

MDX Expression needed

Post by SBK88 » Fri May 12, 2017 9:26 am

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
MVP
Posts: 2322
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: MDX Expression needed

Post by Wim Gielis » Fri May 12, 2017 11:10 am

Code: Select all

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

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

User avatar
gtonkin
MVP
Posts: 761
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: MDX Expression needed

Post by gtonkin » Fri May 12, 2017 3:46 pm

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.
Last edited by gtonkin on Fri May 12, 2017 4:23 pm, edited 2 times in total.

lotsaram
MVP
Posts: 3342
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

Post by lotsaram » Fri May 12, 2017 4:00 pm

gtonkin 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:

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} }
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
gtonkin
MVP
Posts: 761
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: MDX Expression needed

Post by gtonkin » Fri May 12, 2017 4:10 pm

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

Wim Gielis
MVP
Posts: 2322
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: MDX Expression needed

Post by Wim Gielis » Sat May 13, 2017 5:52 pm

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

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

SBK88
Posts: 38
Joined: Fri Apr 17, 2015 5:55 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2013

Re: MDX Expression needed

Post by SBK88 » Wed May 17, 2017 8:13 am

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

Thanks to all of u

User avatar
yyi
Community Contributor
Posts: 117
Joined: Thu Aug 28, 2008 4:42 am
Location: Sydney, Australia

Re: MDX Expression needed

Post by yyi » Thu May 25, 2017 2:14 am

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].
win2012 svr: tm1 v10.2.2 x64, client v10.2.2 x64/PA 2.0, win10, excel 365.

Post Reply