Filter a string cube data in MDX

Post Reply
Posts: 16
Joined: Mon Jan 12, 2015 9:51 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: OFFICE 2007

Filter a string cube data in MDX

Post by wendyWang » Wed Sep 06, 2017 3:51 pm

I have a cube with 3 dimentions, and want to built an active form with dynamic MDX.

CubeA: User, Product, Measure

I set a MDX in dimension User,but no element is showed.
{Filter( {TM1SUBSETALL( [User] )},[CubeA].([Measure].[Department])="DepA")}

If I set a specific Product, the MDX will have result, the changed MDX like this:
{Filter( {TM1SUBSETALL( [User] )},[CubeA].( [Product].[p1], [Measure].[Department])="DepA")}
but there are so many products, and it's impossible for me to specify all the products in MDX, and the syntax is not allowed to write all the products.

Can anybody help me solve this problem? Many thanks.


User avatar
Posts: 2492
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA

Re: Filter a string cube data in MDX

Post by tomok » Wed Sep 06, 2017 4:18 pm

What you've encountered is one of the limitations of MDX subsets in TM1. I suppose what you are looking for is for the MDX to return all the Users where the Department measure has "DeptA", correct? Unfortunately, you can't do this through an MDX subset as it can't iteratively cycle through all products. A subset in the User dimension is only going to know about User. That's why you have to specify an element from all the other dimensions. There is one exception however. Your original subset would work in a cube view that contains both the User and Product dimension, where the User dimension was on the rows or columns and Products was a title dimension. Unfortunately, that's only going to return the Users for a single product, not all products, because a title dimension can, by definition, only point to one element.
Tom O'Kelley - Manager Finance Systems
American Tower

Posts: 1588
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom

Re: Filter a string cube data in MDX

Post by declanr » Wed Sep 06, 2017 4:45 pm

Not entirely true, you can loop through the products dimensions by using the generate MDX statement. So it would be generate on a level 0 product subset then pass that in to your other mdx statement using current member. Not always the most efficient though if the product dimension is large.

Post Reply