TM1 Forum

Hi

I am new to TM1 and I have run in to a problem. I need to filter a dimension using a MDX statement that is refering to a kind of controlcube that I created. That cube consists of two dimensions with consolidated elements with elements below (like a hierarchy). Everything works fine accept for the fact that I would like to have - NOT only current members (elements) but also the consolidated elements that these members belong to.

{FILTER(
TM1FILTERBYLEVEL(
{TM1SUBSETALL([Cost element Group])}
,0),
[CostBudg Controlcube].([CC_and_IO].CurrentMember)
>0
)}

I guess is that it has something to do with "ancestor" but I can not figure it out.How can I write that statement and include not only "CurrentMember" as it is right now, but also the level(s) above (which also has a value greater than zero because they are consolidated).

Please please help.

What dimension are you creating the subset for, Cost element Group or CC_and_IO?
It looks like Cost element Group, in which case you can't use CurrentMember without context on CC_and_IO. That just won't work.

Thank you lotsaram...

Your right, I am filtering the "Cost element Group" dimension. I am not sure what you mean with "you cant use CurrentMember"...

This MDX-function goes to the cube [CostBudg Controlcube] and looks for all Cost element Groups for a specific CostCenter (CC_and_IO) that has a value greater than zero in the intersection. It then returns all of them but on an elementlevel. I would like it to return all levels, in other Words:

Example:

CostCenter X has 5 Cost element Groups connected to it.

Element 1
Element 2
Element 3
Element 4
Element 5

But because of the fact that Element 1 lies below this consolidated element:

Group 1
Element 1

I would like my mdx not only to pick Element 1-5 but also Group 1. Is that not possible?

Here are the solution (eventhough it will not work for me):

{FILTER(
TM1FILTERBYLEVEL(
{TM1SUBSETALL([Cost element Group])}
,0,2,3,4,5,6),
[CostBudg Controlcube].([CC_and_IO].CurrentMember)
>0
)}

That brings all the levels. My problem is that I have the same element under different consolidated levels so my filter doesnt work anyway. No, this is not something that I have decieded....