Page 1 of 1
MDX Get children with Multiple Parents
Posted: Tue Mar 28, 2023 2:25 pm
by MarenC
Hi,
I was wondering if it was possible to write some MDX to list all leaf elements within a dimension that have more than, say, 2 parents?
Have tried a few things but it doesn't look possible to me.
If I was doing this in a TI process I would loop over the dimension and do an elparn against each leaf (which is an okay alternative).
I assume that this would have many caveats, such as dimension structure, but for the sake of simplicity, lets say this was a simple dimension with an All elements root consolidation and a single rollup attached to this root.
Also, for now, don't worry about the parent level.
Maren
Re: MDX Get children with Multiple Parents
Posted: Tue Mar 28, 2023 3:23 pm
by gtonkin
Are you stalking me? Did this just yesterday to meet a requirement:
Basically looking at all the elements in a rollup and counting those that appear multiple times.
You could likely add your own TM1FilterByLevel where/if necessary:
Code: Select all
FILTER
(
{DESCENDANTS({[<dim>].[<hier>].[<rollup>]})},
COUNT(
{INTERSECT(
{DESCENDANTS({[<dim>].[<hier>].[<rollup>]})},
{[<dim>].[<hier>].CurrentMember},
ALL)}
)>1
)
Re: MDX Get children with Multiple Parents
Posted: Tue Mar 28, 2023 5:14 pm
by Steve Rowe
Nice, that's a good one!
Re: MDX Get children with Multiple Parents
Posted: Wed Mar 29, 2023 7:20 am
by MarenC
Hi George,
fabulous! I wasn't stalking you, honest, but doesn't seem like the worst idea in the world when you can help with things like this
Just a note: I added a union and except to your code in order to remove the elements showing multiple times.
I think I know where I went wrong with trying to solve this, I wasn't using the actual rollup in any of my attempts, as in the back of my mind, I thought doing this over multiple rollups would be ideal. I only put a single rollup in my post for simplicity sake, but now thinking about it, one rollup is all I need anyway!
Thanks again
Maren
Re: MDX Get children with Multiple Parents
Posted: Wed Mar 29, 2023 7:54 am
by gtonkin
Glad it helped.
I left the duplicates in as I have ideas of adding more MDX around them to show their parents in the rollup so that it is easy to track down the nonsense. A project for another day...
Re: MDX Get children with Multiple Parents
Posted: Sun Apr 02, 2023 6:04 am
by Wim Gielis
To find the leaf elements that have multiple parents in a rollup, you can consider:
Code: Select all
Filter( TM1FilterByLevel( Descendants( [<dim>].[<hier>].[<rollup>] ), 0 ), TM1TupleSize( Intersect( Descendants( [<dim>].[<hier>].[<rollup>] ), {[<dim>].[<hier>].CurrentMember}, All ).Item(1)) = 1 )
This features the functions TM1TupleSize and Item, which we (I) don't use every day.
Re: MDX Get children with Multiple Parents
Posted: Sun Apr 02, 2023 6:38 am
by Wim Gielis
Should you want to be presented the list of MUNs that apply, it is the answers above.
Should you want to be presented the list of unique element names that apply (keep only one of the results of each one):
Code: Select all
Generate( Filter( TM1FilterByLevel( Descendants( [<dim>].[<hier>].[<rollup>] ), 0 ), TM1TupleSize( Intersect( Descendants( [<dim>].[<hier>].[<rollup>] ), {[<dim>].[<hier>].CurrentMember}, All ).Item(1)) = 1 ),
{ StrToMember( "[<dim>].[<hier>].[" + [<dim>].[<hier>].CurrentMember.Name + "]" ) })
This is a trick of mine to convert a set of duplicate element names, but unique MUNs, to what folks commonly understand when they talk about unique elements.