Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Distinct count Expand / Collapse
Author
Message
Posted Friday, July 11, 2014 5:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 26, 2014 2:27 PM
Points: 4, Visits: 38
Hi all,

Prolly easy question, but for some reason the query below does not return the distinct count but just the count. Should be 16 but it's retruning 17. Any help? :)

WITH SET [MySet] AS
NONEMPTY(
FILTER(
[Geography].[City].[City].Members,
instr(left([Geography].[City].currentmember.name,1), 'A') > 0
),
[Measures].[Internet Order Count]
)

MEMBER [Measures].[CountMembers] AS
DISTINCTCOUNT([MySet])
//why not distinct?

SELECT {
[Measures].[Internet Order Count],
[Measures].[CountMembers]
}
ON COLUMNS,

[MySet]

ON ROWS
FROM [Adventure Works]

Post #1591567
Posted Monday, July 14, 2014 2:46 AM This worked for the OP Answer marked as solution
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:40 AM
Points: 451, Visits: 847
It's returning 17 as there are 17 members in your set.
Augsburg is listed twice but it's still two distinct members that have different properties further up the hierarchy -
[Geography].[Geography].[City].&[Augsburg]&[HH]
[Geography].[Geography].[City].&[Augsburg]&[BY]
One is in the Bayern province and the other is in Hamburg.





I'm on LinkedIn
Post #1592097
Posted Monday, July 14, 2014 6:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 26, 2014 2:27 PM
Points: 4, Visits: 38
Hi,

Thanks for your answer! I found that out yesterday as well after searching a while .. :/

Thanks!
Post #1592167
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse