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, August 30, 2014 9:01 AM
Points: 5, Visits: 48
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
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 505, Visits: 985
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, August 30, 2014 9:01 AM
Points: 5, Visits: 48
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