TM1 Forum

Hi!

I need help writing a correct MDX query to solve the following problem.
I have a cube 01_Confirmations with two dimensions: Departments, Stages.
Departments dimension structure:
-Dep_head (Consolidation)
--Dep_01 (C)
----Dep_01_01 (Numeric)
----Dep_01_02 (N)
--Dep_02 (C)
----Dep_02_01 (N)
----Dep_03_02 (N)
----Dep_04_03 (N)
and so on. Each consolidated Dep-element has several children. Dep_##_## is always leaf elements
Stages dimension contains two string elements ST_01, ST_02.

So, I want to create an MDX subset of elements of Departments dimension, which in the cube 01_Confirmations have the value ST_01='Confirm'. If the subset has consolidated elements (departments), then exclude all its children from the subset.
For example, in the cube 01_Confirmations value 'Confirm' set for Dep_01 (C), Dep_01_02 (N), Dep_04_03 (N). MDX should return Dep_01 and Dep_04_03. Dep_01_02 will not be included in the subset, because subset already has a Dep_01_02 parent element (Dep_01).
I wrote following filter:
FILTER([Departments].[Departments].MEMBERS, [01_Confirmation].([Stages].[Stages].[ST_01])='Confirm')
But I have no idea how to form a subset without children if their parent element is already in the subset. I tried using the EXCEPT function, but I couldn't get the correct result.

Thanks for help.

Can you try ?

Wim Gielis wrote: ↑Wed Jul 17, 2024 8:53 pm Can you try ?

Code: Select all

Except( Filter([Departments].[Departments].Members, [01_Confirmation].([Stages].[Stages].[ST_01]) = "Confirm"), TM1FilterByLevel( Descendants( FILTER( Filter( [Departments].[Departments].Members, [Departments].CurrentMember.Properties("ELEMENT_TYPE") = "3" ), [01_Confirmation].([Stages].[Stages].[ST_01]) = "Confirm")), 0 ))

Wow, nice solution. Thanks

You’re welcome, thanks for the feedback (it was written on the tablet without testing )