MDX Get children with Multiple Parents

[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

[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
)

[Steve Rowe]

Nice, that's a good one!

[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

[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...

[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.

[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.