Distinct member list using MDX

[TomaszB]

Hi Everyone,

Has someone of you encountered a problem with the distinct TM1's MDX set function?
I'm trying to list unique, zero level members from a consolidation.
The consolidation contains repeating elements which were copied to 2 subconsolidations.
While trying to list all the members using the below MDX query, TM1 is still showing duplicates.

Code: Select all

{TM1SORT({DISTINCT( {TM1FILTERBYLEVEL( {Descendants (entity.[Europe])}, 0)})} , ASC ) }

Is there a better way to remove the duplicates?

Regards
Tomasz

[rmackenzie]

Has someone of you encountered a problem with the distinct TM1's MDX set function?

DESCENDANTS has a lot of options and it's not clear how all of them relate to TM1 hierarchies (as opposed to MSAS ones) as TM1 structures its dimensions differently especially in that levels are treated differently.

I'm trying to list unique, zero level members from a consolidation.
The consolidation contains repeating elements which were copied to 2 subconsolidations.
While trying to list all the members using the below MDX query, TM1 is still showing duplicates.

Code: Select all

{TM1SORT({DISTINCT( {TM1FILTERBYLEVEL( {Descendants (entity.[Europe])}, 0)})} , ASC ) }

Basically, I'd avoid DESCENDANTS here and use this more 'boilerplate' bit of MDX instead:

Code: Select all

{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[Entity].[Europe]}, ALL, RECURSIVE )}, 0)}

and then slot that inside your existing statement:

Code: Select all

{TM1SORT(
  DISTINCT( 
    {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[Entity].[Europe]}, ALL, RECURSIVE )}, 0)}
  ),
ASC)}

[TomaszB]

Robin thank you for the answer.

I will definitely avoid the DESCENDANTS and start to use the ALL, RECURSIVE statement.

Unfortunately it isn't solving the problem. The duplicates are still there after using the changed MDX expression and the distinct isn’t filtering it out.
I have created a sample entity which contains only few elements and attached it to my replay.

The attached screen is showing the hierarchy before applying the mdx statement and after updating.
As you can see the all of the elements are doubled.

I tried also to record an expression with the subset editor which gave me a the following statement and a correct result.

Code: Select all

{HIERARCHIZE(
	{TM1FILTERBYLEVEL( 
		{TM1DRILLDOWNMEMBER({TM1SubsetBasis()}, ALL, RECURSIVE )}, 
	0)}
 )}

After replacing the “{TM1SubsetBasis()}” with “{[Entity].[Europe]}” the doubled elements are shown again.

Code: Select all

{HIERARCHIZE(
	{TM1FILTERBYLEVEL( 
		{TM1DRILLDOWNMEMBER({[Entity].[Europe]}, ALL, RECURSIVE )}, 
	0)}
 )}

[Edward Stuart]

I haven't got the DISTINCT function working appropriately but have you considered INTERSECT between 'Total Europe Memo Items' and 'Total International memo items'?

[TomaszB]

I haven't got the DISTINCT function working appropriately but have you considered INTERSECT between 'Total Europe Memo Items' and 'Total International memo items'?

I considered to use the INTERSECT function but it is not always the case that an element from 'Total Europe Memo Items' is in 'Total International memo items'. That’s way the function can't be used.
After few tries I figured out how to archive the expected result.

The function which helped me was UNION().
However, it was not so easy because I had to union more than 2 subconsolidations. For this reason, I had to use nested union functions which finally brought me to a MDX expression with over 600 characters.
The expression was built for an Excel Active form so it exceeded almost 3 times the 255 characters limitation.
Finally I changed the expression to union all zero level elements of the "Europe" consolidation with one element which I'm sure it will be always under the Europe consolidation.

Code: Select all

{TM1SORT(
	{UNION(
		{TM1FILTERBYLEVEL(
			{TM1DRILLDOWNMEMBER( {[lqd_Entity].[Europe]}, ALL, RECURSIVE )}
		,0)},
		{[lqd_Entity].[UK DLG]}
	)},
ASC)}

Thank you all for help!

Tom

[Edward Stuart]

Playing around with this I've found that when you use Union:

Returns a set that is generated by the union of two sets, optionally retaining duplicate members.

When I record the expression and Select All Elements then Level 0, I get the intended result (note I have a hierarchy that starts at Europe):

Code: Select all

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

Amending this to view all elements under Europe changes this too:

Code: Select all

{TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({[Entity].[Europe]},ALL,RECURSIVE)}, 0)} 

Which I've found to return duplicates.

However, adding a Union to this statement removes the duplicates:

Code: Select all

{UNION(
   {TM1FILTERBYLEVEL(
      {TM1DRILLDOWNMEMBER( {[Entity].[Europe]}, ALL, RECURSIVE )}
   ,0)},
   {[Entity].[Europe]}
)}

To include the duplicates you would amend the statement to:

Code: Select all

{UNION(
   {TM1FILTERBYLEVEL(
      {TM1DRILLDOWNMEMBER( {[Entity].[Europe]}, ALL, RECURSIVE )}
   ,0)},
   {[Entity].[Europe]}, ALL
)}