Distinct Count

Last Post 12 Aug 2004 09:04 PM by yocky. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
dhaney40
New Member
New Member

--
08 Apr 2004 01:27 PM
I have a dim design and data as follows.

Dim

School_1
Class_1
Student_1
Student_22
Class_2
Student_1
Student_45


What I need is to be able to do a distinct count at the Class or School level of the Student level members. I am currently using
a Calculated Member with the formula as follows:

DistinctCount({[Dim].[Class].Members}).


This works fine when getting the distinct count at the Class level. But when getting the distinct count
at the School level I double count Student_1. It does a distinct count at each Class then aggregates.
I really need to only count Student_1 one time.

Any suggestion on design of dim or use of Calculated Members / Cells would be much appreciated.

Thanks,

JD
yocky
New Member
New Member

--
12 Aug 2004 09:04 PM
I'll be watching for this result. I've encountered this problem in a retail environment.
jojo_jacob
New Member
New Member

--
28 Sep 2004 07:01 PM
You may try the following. Let me know if this works for you. I couldn't test it as I am away from my desktop.

DistinctCount(Decendents([Dim], 2, LEAVES))

Joe
vhan
New Member
New Member

--
10 Jan 2005 11:58 AM
Have you tried to define this as a calculated measure in the cube itself?
If you use your student_dimension ID as a measure in the cube (call it Distinct_nbr).
In the property definitions in the cube, selct distinct count instead of sum.

This distinct count will hopefully give you a distinct count over all dimensions.

:-)

You have probably already solved this question, since you asked in already in April, but I am new to this forum so I have to see how it is functioning.
You are not authorized to post a reply.

Acceptable Use Policy