Hi, MDX experts !

I just created calculated member on SSAS 2005, named "Distinct sold
articles". I would like to known distinct sold articles by article name and
by brand. Article name hierarchy and brand name hierarchy are in the same
dimension "Article".

When I run by article name, I get correct results, but when I run by brand,
I get the same values for all brands.

--By article description

with member [Measures].[Distinct sold articles] as
count ({[measures].[Sales qty]} * descendants ([Article].[Article
description].currentmember, [Article].[Article description].[Article
description]),excludeempty)

SELECT NON EMPTY
HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({ [Article].[Article
description].[All articles]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON
COLUMNS

FROM [Sales Stock] WHERE ([Measures].[Distinct sold articles],
[Date].[Day].&[610])

--by brand name

with member [Measures].[Distinct sold articles] as
count ({[measures].[Sales qty]} * descendants ([Article].[Article
description].currentmember, [Article].[Article description].[Article
description]),excludeempty)

SELECT NON EMPTY
HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({ [Article].[Brand].[All
brands]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS

FROM [Sales Stock] WHERE ([Measures].[Distinct sold articles],
[Date].[Day].&[610])

I would like to get number of distinct sold articles filtered by each brand.

Btw, if I run the same query in old, AS 2000, I get correct results!!! In As
2000, [article description] is a main dimension, and [Brand] is a virtual
dimension made from [article description].

Thank you!

Ramunas

Hi Ramunas,

Though I don't know the details of attribute relationships in your
[Article] dimension, this issue could be related to the attribute
overwrite behavior in SSAS calculations, as explained in this paper:

http://www.sqlserveranalysisservices...uteRelationshi
ps.htm
Perhaps some explanation is required as to what exactly is an overwrite:
whenever a calculation specifies a member of a hierarchy, this is an
overwrite of the currentmember.
...

Here's an Adventure Works query which I think illustrates your issue -
it attempts to count Products for different Styles, sold on July 1,
2002, but all counts are identical:

count ({[Measures].[Order Quantity]} *
descendants ([Product].[Product Categories].currentmember,
[Product].[Product Categories].[Product Name]), excludeempty)

SELECT NON EMPTY
HIERARCHIZE(AddCalculatedMembers({DrillDownLevel(
{[Product].[Style].[All Products]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [Adventure Works]
WHERE ([Measures].[Distinct Products], [Date].[Date].&[366])
------------------------------------------------------------
All Products Mens Not Applicable Unisex Womens
107 107 107 107 107

But substituting Existing for Descndants() seems to work:

count ({[Measures].[Order Quantity]} *
existing [Product].[Product].[Product], excludeempty)

SELECT NON EMPTY
HIERARCHIZE(AddCalculatedMembers({DrillDownLevel(
{[Product].[Style].[All Products]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [Adventure Works]
WHERE ([Measures].[Distinct Products], [Date].[Date].&[366])
------------------------------------------------------------
All Products Mens Not Applicable Unisex Womens
107 6 26 63 12

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Ramunas,

Though I don't know the details of attribute relationships in your
[Article] dimension, this issue could be related to the attribute
overwrite behavior in SSAS calculations, as explained in this paper:

http://www.sqlserveranalysisservices...uteRelationshi
ps.htm
Perhaps some explanation is required as to what exactly is an overwrite:
whenever a calculation specifies a member of a hierarchy, this is an
overwrite of the currentmember.
...

Here's an Adventure Works query which I think illustrates your issue -
it attempts to count Products for different Styles, sold on July 1,
2002, but all counts are identical:

count ({[Measures].[Order Quantity]} *
descendants ([Product].[Product Categories].currentmember,
[Product].[Product Categories].[Product Name]), excludeempty)

SELECT NON EMPTY
HIERARCHIZE(AddCalculatedMembers({DrillDownLevel(
{[Product].[Style].[All Products]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [Adventure Works]
WHERE ([Measures].[Distinct Products], [Date].[Date].&[366])
------------------------------------------------------------
All Products Mens Not Applicable Unisex Womens
107 107 107 107 107

But substituting Existing for Descndants() seems to work:

count ({[Measures].[Order Quantity]} *
existing [Product].[Product].[Product], excludeempty)

SELECT NON EMPTY
HIERARCHIZE(AddCalculatedMembers({DrillDownLevel(
{[Product].[Style].[All Products]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [Adventure Works]
WHERE ([Measures].[Distinct Products], [Date].[Date].&[366])
------------------------------------------------------------
All Products Mens Not Applicable Unisex Womens
107 6 26 63 12

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi, Deepak,
yes, this is exactly what I want - existing. Thanks.

Ramunas

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote