DISTINCT function does not remove the duplicate member

Hi All,

The following query returns 4 members ([Gross Sales], [Trade Sales], [Intercompany Sales], [Trade Sales]) while it should return only 3. DISTINCT function does not remove the duplicated member [Trade Sales].

SELECT

{[Measures].[Amount]}

ON AXIS(0),

DISTINCT (DESCENDANTS({ [Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales]}, 0, self_and_after))

ON AXIS(1)

FROM [ADVENTURE WORKS]

Any inputs will be appreciated.

David.

DISTINCT must follow SELECT:

Code Snippet

SELECT

DISTINCT (DESCENDANTS({ [Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales]}, 0, self_and_after)) ON AXIS(1),

{[Measures].[Amount]}

ON AXIS(0),

FROM [ADVENTURE WORKS]

Adamus

|||

Seems to be a bug, since if you statically list the members, Distinct() seems to work:

Code Snippet

SELECT

{[Measures].[Amount]} ON AXIS(0),

Distinct({[Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales],

[Account].[Accounts].[Intercompany Sales],

[Account].[Accounts].[Trade Sales]}) ON AXIS(1)

FROM [ADVENTURE WORKS]

Using Generate() instead of Distinct() also seems to work:

Code Snippet

SELECT

{[Measures].[Amount]} ON AXIS(0),

Generate(DESCENDANTS({[Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales]}, 0, self_and_after),

{[Account].[Accounts].CurrentMember}) ON AXIS(1)

FROM [ADVENTURE WORKS]

|||

Thx, Deepak.

The question remains: is it just a bug or there is some hidden meaning to this behavior?

Curiously, if instead of DISTINCT you apply other functions that should remove duplicates,

the duplicates still remain, for example, UNION with an empty set:

UNION(DESCENDANTS({[Account].[Accounts].[Gross Sales], [Account].[Accounts].[Trade Sales]}, 0, self_and_after), {})

Distinct count issue

Hello,
I have made a cube the following calculated member (with as parent member Measures):
COUNT(NONEMPTY( {[Parsed Logs Visitors].[Parsed Logs Visitors Dimension Table].[Parsed Logs Visitors Dimension Table]} *
{[Measures].[Parsed Logs Count]}))

Here I try to make a unique visitor count using the primary key in a non empty crossjoin with the fact table. When I query it with other dimension attributes it works fine, but it gives the total amount of unique visitors when I use (other) attributes of the same dimension.
How can I avoid that?

Thanks in advance,

Eyso Zanstra

You could try applying "existing" to the primary key attribute set:

COUNT(NONEMPTY( {Existing [Parsed Logs Visitors].[Parsed Logs Visitors Dimension Table].[Parsed Logs Visitors Dimension Table]} *
{[Measures].[Parsed Logs Count]}))

|||Yes, that did the trick.
Thank you!

Displaying member name instead of key in query

In the tools that I have used for getting generated MDX queries (Query Analyzer, Proclarity, Excel) the query always seems to show the key value instead of the member name.

Is it possible or is there a query tool that will generate queries with the member name instead?

See example:

[Organization].&[152551]

Should read

[Organization].[By Division]

thanks,

Andrew

Set "MemberNamesUnique" to True for a particular attribute. That should do it|||

Thanks! Didn't even have to reprocess!

cheers,

Andrew

Displaying Character Format in Numeric Field

I have a Calculated Member that displays values of 999.0 when the calculation cannot be computed based on certain criteria. I would like to be able to format the numeric field so that is displays a character such as "X" when the value of the Calculated Member is 999.0. Does anyone know how to do this?

If this is in Analysis Services 2005, you should be able to use the IIF() MDX function to return either the value or "x".|||

Thanks. That worked!

Displaying Character Format in Numeric Field

I have a Calculated Member that displays values of 999.0 when the calculation cannot be computed based on certain criteria. I would like to be able to format the numeric field so that is displays a character such as "X" when the value of the Calculated Member is 999.0. Does anyone know how to do this?

If this is in Analysis Services 2005, you should be able to use the IIF() MDX function to return either the value or "x".|||

Thanks. That worked!