IBM Planning Analytics (PA) subsets can be made dynamic through multi-dimensional expressions (MDX).  This reduces manual maintenance and ensures that subsets display the latest metadata and data available in the model. For example, when the Current Year of a model is modified, an MDX driven “Current Year Months” subset will be updated immediately.

Today we will walk through 5 powerful MDX functions and use cases for each of them.

1. DESCENDANTS: Returns the element specified and all descendants of the element

Objective: In the Periods dimension, return all children of the element “FY2022” and “FY2022”
MDX: {DESCENDANTS ([Periods].[FY2022] )}

2. FILTER: Filters elements based on cube values
Objective: In the Periods dimension, pull the element specified as “Current Year” in the Global Settings cube and all its descendants
MDX:
{ DESCENDANTS ({ FILTER ({ TM1SUBSETALL ( [Periods] )}, [Periods].CURRENTMEMBER.PROPERTIES(“MEMBER_CAPTION”) =
[Global Settings].([Global Settings].[Current Year],[Global Settings Measures].[String] ))} )}

Global Settings Cube:

Result:

3. UNION: Returns a union of 2 or more specified sets
Objective: In the Periods dimension, pull the elements specified as “Current Year” and “Next Year” in the Global Settings cube and their descendants

MDX:
{ UNION (

{ DESCENDANTS ( { FILTER ({ TM1SUBSETALL ( [Periods] )}, [Periods].CURRENTMEMBER.PROPERTIES(“MEMBER_CAPTION”)  = [Global Settings].([Global Settings].[Current Year],[Global Settings Measures].[String]))} )} ,

{ DESCENDANTS ( { FILTER ({ TM1SUBSETALL ( [Periods] )}, [Periods].CURRENTMEMBER.PROPERTIES(“MEMBER_CAPTION”)  = [Global Settings].([Global Settings].[Next Year],[Global Settings Measures].[String]))} )} )}

Result:

Alternative MDX using FILTER:
{ DESCENDANTS ( { FILTER ({ TM1SUBSETALL ( [Periods] )}, [Periods].CURRENTMEMBER.PROPERTIES(“MEMBER_CAPTION”)  @>= [Global Settings].([Global Settings].[Current Year],[Global Settings Measures].[String]) AND [Periods].CURRENTMEMBER.PROPERTIES(“MEMBER_CAPTION”) @<= [Global Settings].([Global Settings].[Next Year],[Global Settings Measures].[String])  )} )}

4. TM1FILTERBYLEVEL: Filters the subset based on a specified level
Objective: In the Periods dimension, pull only level 0 descendants of the elements specified as “Current Year” and “Next Year” in the Global Settings cube.

MDX:
{ TM1FILTERBYLEVEL ( { DESCENDANTS ( { FILTER ({ TM1SUBSETALL ( [Periods] )}, [Periods].CURRENTMEMBER.PROPERTIES(“MEMBER_CAPTION”) @>= [Global Settings].([Global Settings].[Current Year],[Global Settings Measures].[String]) AND [Periods].CURRENTMEMBER.PROPERTIES(“MEMBER_CAPTION”) @<= [Global Settings].([Global Settings].[Next Year],[Global Settings Measures].[String])  )} )} , 0 )}

Result:

5. EXCEPT: Evaluates two expressions, retains elements from the first expression, and removes elements from the second expression
Objective: In the Periods dimension, pull only level C descendants of the elements specified as “Current Year” and “Next Year” in the Global Settings cube.MDX:
{ EXCEPT (

{ DESCENDANTS ( { FILTER ({ TM1SUBSETALL ( [Periods] )}, [Periods].CURRENTMEMBER.PROPERTIES(“MEMBER_CAPTION”)  @>= [Global Settings].([Global Settings].[Current Year],[Global Settings Measures].[String]) AND [Periods].CURRENTMEMBER.PROPERTIES(“MEMBER_CAPTION”) @<= [Global Settings].([Global Settings].[Next Year],[Global Settings Measures].[String])  )} )} ,

{ TM1FILTERBYLEVEL ( { TM1SUBSETALL ( [Periods] )} , 0 )} )}

Result:

How will you use these MDX functions? We would love to hear about your use cases and any other MDX functions you are interested in!