Help with dynamic subset

[mmckimson]

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

[lotsaram]

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] ))
  )}
)}

[tomok]

Just add this to the front of your MDX:

{[dimProducts].[Total Products]} +

[mmckimson]

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