Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

distinct counts on measures

Status
Not open for further replies.

Bygbobbo

Programmer
Apr 23, 2002
145
US
I am looking for the correct sintax that will create a distinct count of a field using a calculated measure. Or any other syntax...

thanks in advance
 
Hi,
This example I have got from my good fried...it will be marked by green...I don't want to take the ownership on solution that I have not discovered :):

This example illustrates how to use distinct count on SqlServer 7.0 - SqlServer 2000 has the function and it works much faster (x100)

If we have a cube {VistorID, Day} we can get distinct count by using the following MDX query:

"With member
Measures.DistinctCount as
'Count(CrossJoin(
Descendants( [Visitor Id].CurrentMember, [Visitor id]),
{[Measures].[Visitor Id]}),
EXCLUDEEMPTY)'
select

Union(Day.Year.Members, Day.Month.Members) on Columns,
{[Measures].[Visitor Id], Measures.DistinctCount} on Rows

from Visitors"


till now my friends' example.

My commentary
I tryed this, it works but it is not useful for me because here you can really make the distinct count of members of dimension, so I don't want to create dimension for userid if you have 1 million of users.

But if you want to do that for such dimension that is not big and its members number is not growing all the time
try this...

Please, respond me if it was helpful for you
good luck


Additionally Issahar
senior software engineer
Israel
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top