TM1 Forum

Hi,
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.

wendyWang

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.

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.