MDX: Filter by multiple elements in same dimension

[JamiseBondi]

Hi,

I'm trying to show all elements that have a non-zero value for two elements in a measures dimension (MAT1).
The code:

{FILTER
( {TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( { DRILLDOWNMEMBER( {TM1SubsetBasis()}, {[TL].[AB00]} ) }, ALL, RECURSIVE )}, 0)}, ASC)} ,
[AMAT1].([VER].[2014_1],[MAT1].[Price - Company Currency] , [MAT1].[Price - Group Currency] ) <>0 )
}

I'm getting "Duplicate dimension in tuple" error because I'm trying to show all elements in the TL dimension that have a non-zero value for the two elements in the measures dimension [MAT1].[Price - Company Currency] , [MAT1].[Price - Group Currency]

enclosing the two dimension elements in " " doesn't seem to help either "[MAT1].[Price - Company Currency] , [MAT1].[Price - Group Currency]"

Any ideas of how I can specify multiple elements within the same dimension in this MDX?
I've read through http://www.bihints.com/book/export/html/68
but didn't come across this example.
Thanks guys.

[EvgenyT]

HI mate,

As far as I know you can only reference the same dimension once as in your example for TM1Sort function.

Just use OR/AND and extend your query to have something along these lines:

[AMAT1].([VER].[2014_1],[MAT1].[Price - Company Currency] ) <>0 AND [AMAT1].([VER].[2014_1],[MAT1].[Price - Group Currency]<>0)

OR

[AMAT1].([VER].[2014_1],[MAT1].[Price - Company Currency] ) <>0 OR [AMAT1].([VER].[2014_1],[MAT1].[Price - Group Currency]<>0)

Depending which condition you want to meet here...


Thanks

ET

[tomok]

When you were reading the MDX Primer on BI Hints did you notice the UNION MDX function? Couldn't you UNION the two MDX queries, one that filters on Price - Company Currency and then another on Price - Group Currency?

[JamiseBondi]

Thanks to both of you guys, you're both correct, I was fighting with the syntax of OR when I saw your example - UNION would also have worked,
thanks guys.
Gotta love this forum.

[lotsaram]

UNION if you are after non-zero for one or the other measure (OR), INTERSECT if you are after non-zero for both measures (AND).

[JamiseBondi]

It appears I was too eager with my first finding - it's not working as expected. I've tried UNION to see if my results will be more accurate (I was getting elements from the wrong consolidator using OR.

Latest attempt:
{FILTER (
{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( { DRILLDOWNMEMBER( {TM1SubsetBasis()}, {[TL].[AB00]} ) }, ALL, RECURSIVE )}, 0)}, ASC)},
{UNION(
[AMAT1].([VER].[2014_1],[MAT1].[Price - Company Currency] ) <>0 ,
[AMAT1].([VER].[2014_1],[MAT1].[Price - Group Currency]) <> 0
) }
)}

I'm trying to get all elements in TL dim under the AB00 consolidator with non-zero values - either with a value of company or group currency. This subset should only look under the AB00 consolidator (for the first subset - I'll have another subset looking under the AM00 consolidator).

I have two consolidators here: AB00 and AM00 in TL dim. The below expression was populating with elements from AM00 (OK) but when I edited it to look at AB00 it was still populating with elements from AM00:
{FILTER (
{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( { DRILLDOWNMEMBER( {TM1SubsetBasis()}, {[TL].[AB00]} ) }, ALL, RECURSIVE )}, 0)}, ASC)} ,
[AMAT1].([VER].[2014_1] , [MAT1].[Price - Company Currency] ) <> 0 OR [AMAT1].([VER].[2014_1] , [MAT1].[Price - Group Currency] ) <>0
) }

Any ideas on why the second statement is ignoring the consolidator or how to get the UNION statement to work?
Thanks.

[lotsaram]

It appears I was too eager with my first finding - it's not working as expected.

Well based on the code you posted it is working as you SHOULD expect it to. Why don't you take the time to read the documentation such as MDX Primer or Microsoft MDX function help library? It would be more efficient.

Code: Select all

{UNION(
  { Set 1 },
  { Set 2 }
) }

[bplaia]

Latest attempt:
{FILTER (
{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( { DRILLDOWNMEMBER( {TM1SubsetBasis()}, {[TL].[AB00]} ) }, ALL, RECURSIVE )}, 0)}, ASC)},
{UNION(
[AMAT1].([VER].[2014_1],[MAT1].[Price - Company Currency] ) <>0 ,
[AMAT1].([VER].[2014_1],[MAT1].[Price - Group Currency]) <> 0
) }
)}

try TM1SUBSETALL( [TL]) instead of TM1SubsetBasis()

[lotsaram]

try TM1SUBSETALL( [TL]) instead of TM1SubsetBasis()

Clearly you have some sort of deeper insight on MDX that the rest of us aren't privy to.

While I don't disagree that keeping TM1SubsetBasis is not a good idea it is not the cause of the problem, rather the incorrect use of union.

[EvgenyT]

Clearly you have some sort of deeper insight on MDX that the rest of us aren't privy to.
While I don't disagree that keeping TM1SubsetBasis is not a good idea it is not the cause of the problem, rather the incorrect use of union.

Yeap, while TM1SubsetBasis will not cause an error in the subset editor, its not a recommended practice to use it e.g TI, active form. I dont need to explain why, do I ?

Also I noted your doing a lot of drilling on that [TL].[AB00] consolidation, if you include it as part of your filter query i.e:

{UNION(
[AMAT1].([VER].[2014_1],[MAT1].[Price - Company Currency],[TL].[AB00] ) <>0 ,
[AMAT1].([VER].[2014_1],[MAT1].[Price - Group Currency],[TL].[AB00]) <> 0
) }

The filter will have the same effect, eliminating the need for extra mdx functions, hence less maintenance ( I know those brackets and commas must drive you insane )

Also I would suggest to define every dim/element(or consolidation) in your Filter query for [AMAT1] cube, on many occasions I have experienced unpredictable results with this kind of mdx queries, once I have specified every dim/elem combination it always returned expected results.

Would you guys agree?

ET

[JamiseBondi]

Thanks for the posts.

I found the original MDX statement tended to ignore the AB00 consolidator to show elements from and showed them from the entire dimension, see below:
{FILTER (
{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( { DRILLDOWNMEMBER( {TM1SubsetBasis()}, {[TL].[AB00]} ) }, ALL, RECURSIVE )}, 0)}, ASC)} ,
[AMAT1].([VER].[2014_1] , [MAT1].[Price - Company Currency] ) <> 0 OR [AMAT1].([VER].[2014_1] , [MAT1].[Price - Group Currency] ) <>0
) }

This statement however shows results only from the AB00 consolidator as I was wanting:
{FILTER (
{TM1DRILLDOWNMEMBER( {[TL].[AB00]}, ALL, RECURSIVE )} ,
[AMAT1].([VER].[2014_1] , [MAT1].[Price - Company Currency] ) <> 0 OR [AMAT1].([VER].[2014_1] , [MAT1].[Price - Group Currency] ) <> 0
) }

The UNION attempt didn't work out:
{UNION(
[AMAT1].([VER].[2014_1], [TL].[AB00], [MT].[All MT],[MAT1].[Price - Company Currency]) <>0 ,
[AMAT1].([VER].[2014_1], [TL].[AB00], [MT].[All MT],[MAT1].[Price - Group Currency]) <> 0
) }

Both with { } around the sets and without, with the MT consolidator and without...
After trying numerous variations on the syntax for UNION I threw in the towel as I couldn't see where the error was (character position 7) (Lotsaram, I did read the MDX primer and searched the Cognos TM1 reference guide - please give people a little more credit for their efforts instead of being condescending. We, who are less 'enlightened' than you, appreciate the pointers and tips and tricks, but don't need a wrap on the knuckles. So - thanks for the pointer on the syntax but ease up a little

Thanks ET for the simplification pointer - appreciated

[EvgenyT]

The UNION attempt didn't work out:
{UNION(
[AMAT1].([VER].[2014_1], [TL].[AB00], [MT].[All MT],[MAT1].[Price - Company Currency]) <>0 ,
[AMAT1].([VER].[2014_1], [TL].[AB00], [MT].[All MT],[MAT1].[Price - Group Currency]) <> 0
) }
Thanks ET for the simplification pointer - appreciated

Hi Mate,

Not a problem.

I want to point out that the mdx expression above doesn't really make sense....Ok, its a union, but a union of what? Please have a look at mdx primer again for an example.

Union takes a set of 2 expressions and what you are trying to do here is to UNION two filters based on the cube value of certain dims/elements combinations, however as you are probably starting to realize by now...there is no FILTER expression in the query above Hence the mdx compiler is saying WTF

Thanks

Evgeny

[JamiseBondi]

I see that now, thanks ET. Noted and appreciated