Dynamic subset by two dimensions

Post Reply
kastellu
Posts: 29
Joined: Thu May 26, 2011 5:38 pm
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2008

Dynamic subset by two dimensions

Post by 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
MVP
Posts: 3667
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Dynamic subset by two dimensions

Post by lotsaram »

Won't zero supression get you what you want?
kastellu
Posts: 29
Joined: Thu May 26, 2011 5:38 pm
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2008

Re: Dynamic subset by two dimensions

Post by 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
MVP
Posts: 195
Joined: Wed Jul 22, 2009 10:35 pm
OLAP Product: TM1
Version: 9.5.2 FP3
Excel Version: 2010

Re: Dynamic subset by two dimensions

Post by jstrygner »

kastellu wrote: 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]
Post Reply