.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Distinct Count

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :


I created a basic Distinct Count Measure for my customers - i.e. a customer distinct count using the default measures created in Analysis Services 2008 (not a calculation).

It works perfectly however, when used in conjunction with the below;

CREATE MEMBER CURRENTCUBE.[Time].[Time By Month].[Year-To-Date (Month)]
 AS SUM(YTD(strToMember("[Time].[Time By Month].[month].&[" + str(year(Now())) + "-" + format((month(Now())), "00") + "-01T00:00:00" + "]").LAG(1)),[Measures].CurrentMember),
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Time';                                                                                                                           

View Complete Post

More Related Resource Links

How can I GROUPBY Branch, then by Client and then by Referral Type and COUNT DISTINCT LPS # in JavaS

Hi, I have an array in JavaScript. How can I GROUPBY Branch, then by Client and then by Referral Type and COUNT DISTINCT LPS # in JavaScript. Please note I need to do this client side using JavaScript. Array Data: Branch Client    Referral Type LPS # 402036   402430    Psychological File Review 30 402049   402805    In-Home Assessment 10.87927 402050      402993   Chiropractic Assessment 100 402049 402805   Chiropractic File Review 10.88054 402049 402806   Chiropractic File Review 10.88055 402049 402806   Defense Medical 20 402049 402807   Chiropractic Assessment 10 402049 402807   In-Home Assessment + Form 1 10.88054 402049 402807   Physiotherapy Assessment 10 402049 402808   Attendant Care Assessment 10 402049 402808   Chiropractic Assessment 10.88041 402049 402808   In-Home Assessment 10 402049 402816   Chiropractic Assessment 10.85316 402049 402827   Neurological File Review 10.88047 402049 402827   Social Work File Review 10.88044 402050 402605   Psychological File Review 880434 402050 402661   Physician Assessment 878203 402050 402993   Physician A

SSAS 2008 Measure group Distinct count

Hi all, I have a data of as number of trasactions,DD, SO, BouncedDD, CancelledDD all are in count (number) while adding these measures manullay to a measure group I have selected usage as DistinctCount for one measure and for all the remaining measures as DiscinctCount.While deploying the cube it shows error as "Fact table canot have more than one distinct count"  

MDX count distinct calculation where another column


Hi All,

i have a fact table like the following


1-episodeKey (not unique)

2-Readmission (takes value null or 'readmission' may i chaneg to (0 or 1) if that help solve the problem)


4-other dimesnions foreign keys


i want to count distinct episode keys where readmission is not null,

so how can i write a calculation that make that count ?




Ahmed Salah

How to implement distinct count with AND/OR clause?



I'm trying to implement distinct count in MDX together with OR clause.
For instance:

I have two "questions" that I want answered via MDX.

1) Number of customers that are individuals and have at least 1 transaction

2) Number of customers that are from Serbia and have at least one arrangement

mdx 1:

with Member segmentCount as
			[Customer].[Customer].Children,[Measures].[TransactionCount] > 0)
Select segmentCount on columns FROM [CPM] 
WHERE ([Customer].[IsIndividual].[IsIndividual].&[1])
result: 35572

 mdx 2:

with Member segmentCount as
			[Customer].[Customer].Children,[Measures].[ArrangementCount] > 0))
Select segmentCount on columns FROM [CPM] 

Distinct Count - MDX



I have requirement to count of number of new products which are been sold in current quarter with respect to previous quarter.

For ex: if i have sold 10 unique products in 2010Q1 and in 2010Q2 14 product are been sold in which 4 product are newly introduced...so i want the end result count to be 4.



AGGREGATE over a time period: how to behave like a distinct count ? (aggregate on the full period an


I would like to compute a measure "MyMeasure" over the last 12 months :

Aggregate({[Event DATE].[Calendar].CurrentMember.Lag(11):[Event DATE].[Calendar].CurrentMember},[Measures].[MyMeasure])

MyMeasure is not a distinct count, so the results returned is the SUM of the distinct count performed on each month over the last 12 month.

What I would like to have is the distinct count over a full year (all months taken together).

For example, suppose that MyMeasure is a simple measure that always returns 1.

The aggregation returns 12 (1 for each month then SUM). I would like it to return 1 (measure on the whole period).

Can somebody help me that's really a very big issue I have to fix !


calculated member-distinct count


I have a product dimension table whose level is category-brand-product id. I have a fact table at lowest level product id and measure sales amt which can have positive and negative values.

I need distinct count  of brands for a particular category whose sales is greater than 0.

fact table data:                                   product table
productid salesamt                              category brand   productid
1             50                                          c1         b1         1
2             100                &nb

Distinct Count with Filtering IDs



Please bear with me as I am newbie in SSAS/MDX. I want DISTINCTCOUNT on ID column of my fact table but after filtering out -1 i.e. unknown IDs.
Here are my tables

ID  Name
-1  Unknown
1   Joe
2   Tim
3   Paul
4   Zina

ID Name
-1    Not Registered
11   Maths
22   Science
33   Arts

DimStudentID  DimCourseID  Marks
1                    11                   89
1                    22                   87
1                    33       &n

Distinct Count on column

Hi I have this Query which is getting me good results ...

      CASE WHEN rn = 1 THEN o_Error_Stmt ELSE '' END AS Error_Stmt,
      CASE WHEN rn = 1 THEN o_Error_Number ELSE '' END AS [Error_Number],
      CASE WHEN rn = 1 THEN o_Count ELSE '' END AS [Count],Membership_Number
      INTO  Stage2_Processing_Errors_Final_Result
SELECT o_Error_Stmt, o_Error_Number,O_Count,Membership_Number
,ROW_NUMBER() OVER(PARTITION BY o_Error_Stmt, o_Error_Number ORDER BY Membership_Number) AS rn
 Select  Distinct 
Error_Stmt AS o_Error_Stmt,
CAST([Error_Number] AS varchar(15)) AS O_Error_Number,

Select Distinct with Count


This is something I can easily do in SQL, but I'm pretty new to LINQ and haven't been able to find an answer online.

I need a LINQ query that returns two values; the first a distinct value and the second the count of the first value. This is filtered by a parameter. Here is my table:















East Sussex

In SQL I use the following:

FROM My_Table

WHERE State = @State

If @State is equal to Hampshire I get the following result





I have managed to create a query that brings back a distinct City;

var recordset = (from s in MyTable where s.State == state select new { s.City,&nb



I want to do a DISTINCT COUNT of Subsbribers who have desposited over £450. I am trying this but just get the #VALUE Error


DISTINCTCOUNT(FILTER([Subscriber].[Subscriber].[Subscriber Key], [Measures].[CardDepositAmount] > 450))


Any  ideas?




Same measure group for count and distinct count in SSAS


Hello there,

I need to make DistinctCount aggregation of few columns in the same fact table in SSAS. For each DistinctCount it creates a new measure group. I need to put all these under same measure group. Please let me know how to go ahead.



How to create a new measure group for distinct count measure?


There is a warning info on my distinct count measure saying "break distinct count measure to a new group to improve performence...", but When I create a new measure group, I cannot choose the fact table which contains the distinct count measure field, as there is already a measure group using this fact table.

Then how could I remove this warning info?

Regards! directfriends.net

Print row count on Label



I have an SP that simply performs a COUNT(*) from a table. I'm am trying to print that number in an ASP Label. It works in Query analyzer but I cant wrap my head around how to display it in a label. Any guidance much appreciated. 

Thank you. 

Count words in the ascx file



I have 163 .resx files. I want to count the words in these files, because my want to translate the sentences (my task is the localisation).

Now I'm opening each file in Ms Visual Studio and I count words manually. How can I count words in all files? And the second question: can I use a program what grab strings from .resx into an editor where I can translate them and save bacn into .resx files? Or must open the files one by one and edit the text fields manually?

Thanks for helping, unfortunately I'm not a programmer.

How to display a count of search results with keywords in a label?


Sorry for a basic post here; I am very new to VB!

I am currently building a search page, which includes a textbox and two drop-down lists, a search button, and a datagrid. If results are found and displayed, I would like a label to display:

(e.g.) "24 results found for 'blue sky clouds' "

...where the integer represents the results count, and the keywords and/or drop-down list selections are included in the string.

If no results are found I would like the label to display:

(e.g.) "No results were found that match your selection. Please try again"

I would also like the label to be invisible until a search occurs.

Thanks in advance!

Editor's Note: Rich Clients Count Too


It's the dead of winter here at the MSDN Magazine offices, and we're having a blast. Temperatures are making their daily struggle to stay above zero. The tempting aroma of chestnuts roasting on an open subway ventilation grate fills the city with a wintry mix of excitement and anticipation.

MSDN Magazine February 2003

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend