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!