TM1 Forum

Posted: **Tue Sep 25, 2012 6:51 pm**

I'm a relative newbie, so excuse the ignorance! To meet end user requirements, I have a list of products and have created the following query to build a dynamic subset based upon two product attributes:

({
FILTER(
{TM1SUBSETALL( [dimProducts] )},
(
([dimProducts].[ProductLine] = [dimProductLine].[ProductLine])
AND
([dimProducts].[Category] = [dimProductCategory].[Category] )
)
)
})

However, while the query works fine showing only relevant products when in the cube, I would like to also have it show 'Total Products' as a part of the subset, where 'Total Products' is of course the aggregation of all products.

If anyone can point me in the right direction, it would be much appreciated.

Mike

Posted: **Tue Sep 25, 2012 7:39 pm**

Taking for granted that you say your existing MDX works to filter your products you could do either of the following. If you want Total Products after the list not before then just change the order.

Code: Select all

{
  [dimProducts].[Total Products],
  {FILTER(
    {TM1SUBSETALL( [dimProducts] )},
    (([dimProducts].[ProductLine] = [dimProductLine].[ProductLine])
    AND
    ([dimProducts].[Category] = [dimProductCategory].[Category] ))
  )}
}

OR

Code: Select all

{[dimProducts].[Total Products]}
+
{FILTER(
  {TM1SUBSETALL( [dimProducts] )},
  (([dimProducts].[ProductLine] = [dimProductLine].[ProductLine])
  AND
  ([dimProducts].[Category] = [dimProductCategory].[Category] ))
)}

OR

Code: Select all

{UNION(
  {[dimProducts].[Total Products]},
  {FILTER(
    {TM1SUBSETALL( [dimProducts] )},
    (([dimProducts].[ProductLine] = [dimProductLine].[ProductLine])
    AND
    ([dimProducts].[Category] = [dimProductCategory].[Category] ))
  )}
)}

Posted: **Tue Sep 25, 2012 7:43 pm**

Just add this to the front of your MDX:

{[dimProducts].[Total Products]} +

Posted: **Tue Sep 25, 2012 7:57 pm**

tomok wrote:Just add this to the front of your MDX:

{[dimProducts].[Total Products]} +

I added an attribute called TotalFlag, applied a 'Yes" to Total Products and added that to my MDX. While it worked, your solution is simpler and would allow me to add other subtotals if they were ever added to the product dimension, so I used it. Thanks!
Mike

TM1 Forum

Help with dynamic subset

Help with dynamic subset

Re: Help with dynamic subset

Re: Help with dynamic subset

Re: Help with dynamic subset