TM1 Forum

Hi everyone,

i have a problem and maybe you guys could help me

Ich habe a Cube with 4 Dimensions. And now I want a list of Elements in DIM1 which include a String Value in the Cube.

for example:

This works, but i have to write down all Elements. But i want, for example, that the MDX includes all Elements in DIM3 and DIM4 not only the Element3 and Element4.
Because it is a String i am searching for, i can't select the TopElement of DIM3 and DIM4

{FILTER( { TM1FILTERBYLEVEL( {TM1SUBSETALL( [DIM1] )}, 0)}, [Cubename].([DIM2].[Element2],[DIM3].[Element3], [DIM4].[Element4] )="hallo")}

Memo

Hi

Not sure if this is what you want but in TM1 instead of putting [Dim].[Element] you can put [Dim].[Subset] so if you define a subset with all base level elements for each dim and specify that, it may give you what you are after

Regards

Paul

Hi,
thanks for that. I will try it next week.

I read, i have to list all dimensions in the MDX Statement. If i don't list one, MDX picks up the Element with the Index 1. Either way it is not i want to do. But i try that with the Subsets.

Thanks

If you remember that values are stored in cubes at the intersection of a single element in each dimension then you'll understand why you have to provide TM1 with the necessary information with which to retrieve what you want. Writing an MDX query to pull back an element from a dimension, based on values from a cube, without providing an element from all the dimensions in the cube is like expecting a DBRW formula to work when missing some parameters. TM1 has to know where to look in the cube, If you don't tell it then the query won't work.

The only time a cube value filtered MDX subset query will work without specifically naming an element from all the dimensions is one used as part of a cube view, where the missing elements are in dimensions that are part of that cube and those dimensions are part of the title section of the view. In this case TM1 will automatically plug in the missing elements in the MDX query from the title section of your cube view. Keep in mind this subset, when looked at through the subset editor, will always evaluate to empty. It will only work in a cube view.

@tomok, my belief is that if you specify a default member in (weirdly) }HierarchyProperties, you can issue an MDX query not mentioning that dimension and still get output. One of the MSAS concepts I think is good ( ). Having said that, my (next) belief is that this is only relevant to the frontends which issue MDX SELECT queries - ie not dynamic subsets.