Dynamic subset by two dimensions

[kastellu]

Hi,

Is it possible to create dynamic subset by filtering two subsets:

Dim1 AtrCode
Group1 A
Group2 B


Dim2 AtrDim1Code
Product1 A
Product2 A
Product4 A
Product5 B


If I create subseti for Dim2, the dynamic subset would look like this.
{FILTER( {TM1SubsetAll( [Dim2] )}, [Dim2].[AtrDim1Code] = [Dim1].[AtrCode])}

So if select specific Group from dim1 only the list of the products from that group is listed. This works fine.

Now I would like to do it from other dimension at the same time. So the subset for Dim1 would look like this:
{FILTER( {TM1SubsetAll( [Dim1] )}, [Dim1].[AtrCode]=[Dim2].[AtrDim1Code])}

But this doesn't work. Why I would like to do it this way, because when you nest both dimension you always get all the products under each group.



Is this possible?

Thanks

[lotsaram]

Won't zero supression get you what you want?

[kastellu]

No, i dont want to do it by using suppress zero, since this will hide some of the fields i still want them to be visible.

[jstrygner]

If I create subseti for Dim2, the dynamic subset would look like this.
{FILTER( {TM1SubsetAll( [Dim2] )}, [Dim2].[AtrDim1Code] = [Dim1].[AtrCode])}

So if select specific Group from dim1 only the list of the products from that group is listed. This works fine.

Well, I was very surprised you wrote it works, but I checked, and it really does! I don't know if I was the only one here that didn't know that, but... I really did not

TM1 somehow knows the attribute of picked Dim1 element in the Title combobox and displays appropriate Dim2 elements in Rows...
What is more, it does it in the context of a view, as I opened another view where I switched from Group1 to Group2 - both views behaved good displaying different rows.

I also created this second subset you wrote.
It does not work only when in the Cube Viewer you have both subsets set to those dynamic ones, you gave examples of. But when you will switch Dim1 with Dim2 (Dim2 goes to Title and Dim1 goes to Rows) and you "turn off" the dynamic subset for Dim2 and turn on for Dim1, it also works good.
Like there would be a problem with circular reference when you have both turned on.

This "dynamic filtering" works only when the dimension with dynamic subset turned on is either in Rows or Columns and this one without dynamic subset is in Title.

[Checked on 9.5.1 HF17]