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

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

Same measure group for count and distinct count in SSAS

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

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.



View Complete Post

More Related Resource Links

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"  

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

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

Optimize Calculated Measure containing COUNT EXISTING

Hi, My goal is to change the text color of all cells that contain aggregated values. Currently I achieve it like this: I COUNT the members of all attributes of all dimensions. To be multi-select-safe I am using the EXISTING keyword. If the members count of at least one dimension is not 1 than the background color of the cell is changed. CREATE MEMBER CURRENTCUBE .[Measures].[SingleCellSelected]   AS iif ((COUNT (Existing ([Dim1].[Attr1].[ Attr1].MEMBERS ))=1) AND (COUNT (Existing ([Dim1].[Attr2].[Attr2].MEMBERS ))=1) AND (COUNT (Existing ([Dim2].[Attr3].[Attr3].MEMBERS ))=1) AND (COUNT (Existing ([Dim2].[Attr4].[Attr4].MEMBERS ))=1) AND (COUNT (Existing ([Dim3].[Attr5].[Attr5].MEMBERS ))=1),1,0),   VISIBLE = 0  ;    SCOPE ([Measures].AllMembers ); FORE_COLOR (this ) = iif ([Measures].[SingleCellSelected]=1,0,16744448); END SCOPE ; This approach works but it performs badly with attributes with many members. Do you have any idea how to optimize this? Thank you!

Measure - New Customer Count

I need to create a measure which counts the number of new customers for each time period. My fact table contains the customer number and its easy to create a distinct count of customers per month/week/year. I'm thinking I need to obtain the first order date for the given customer and compare if the select period is within the time frame, then include or exclude.  

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

Linq to SQL group by category and count each subcategory


I am wonder how I can generate a result table from linq to sql as below

Car sales table
Toyota              small
Toyota              passenger
Toyota              small
Honda               small
Honda               small
Honda               passenger
Honda               small

result table
Brand                small            passenger     total
Toyata                2                  1                     

How to get group count and total count using one select statement?


How to obtain the resultset using one select statement?


Table 1







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] 

Stored procedure help (group by, sub queries, count etc...)


Hi There,

im writing a stored procedure.... Given a groupID, i want to get all the groups from the groups table and also two extra columns....

the first extra column is UserCount, this goes off to the user's table and pulls out a user count for the group.

the second extra column is GroupCount. This goes away and pulls out all other groups that have a ParentID as the incoming org.

I know the stored procedure I have supplied isn't correct, im suspecting I need to have sub queries and group by's.... but sql isn't my forté.

The reason I need these properties returned this way is so that LinqToSql can create a custom object based on this return.

ALTER PROCEDURE [dbo].[uspGetTreeViewGroups]
	-- Add the parameters for the stored procedure here
	@GroupID int

				   WHERE u.GroupID = @GroupID
				   AND u.Enabled = 1) as UserCount,
				   (SELECT COUNT(*) FROM tblGroup g
					WHERE g.ParentID = @GroupID) as ChildCount
	FROM tblGroup g



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.



IN SSAS 2005, total count is incorrect for a partial month



IN SSAS 2005, when I navigate Cube->browser and I drag my fields "Day of week" to rows and couple of measures namely "M1" and "M2" to columns

and applied a filter "hierarchydate" in the filter section(eg partial month Jan 10 to Jan31), I get the total count incorrect. I also have the following observations

1) for example "Instead of giving Sunday "Sub total" for that partial month i.e "Jan 10 to Jan31", it is displayed with all the Total Sunday's count for all the years

2) IN Cube-> browser , it is working fine, if I apply the filter in "select dimesion" section instead of applying filter in "drop filter fields section"

Is there any such kind of issue in SSAS 2005?

Please reply me ASAP if you have any information

count number of columns in group



I need to count number of columns in group -

I have a matrix with SUM in the date area.

I have a group column (lats say 'city') and under it my columns (lats say 'street name');

Under each 'street name' I have cell with SUM function.

The problem is:

When The main group (city) is not expand I get the sum of all cities and what I want to get is an average .

So what I thought of was to divide that number in the number of streets under each city group.

How can I get that number?


Group by with Count(*)

















QUERY:-   TO GET NO.OF REGISTRATIONS BETWEEN PARTICULAR DATE (WHERE Start and End MONTH, YEAR ACCEPTED FROM INTERFACE) BELOW stored procedure is used. Just now it gets all records where the RegistrationDate IS between Start and End Dates AND Status='Expired' not considering ExpiryDate. So it is not getting correct expired users with this query.

As per the requirement it should

1. Count No. of Registered users through 'Microsoft' --working fine just now.

2. Count No. of Registered users through 'Java' - working okay.

3. Count No. of Expired users BETWEEN START MM/YYYY AND END MM/YYYY -- Need Help.

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

SSAS - MDX - need to generate a count for the life of a record



I'm struggling trying to create a calculation which will show what I want.  My requirement is to show a count of 1 while a record is active, and 0 when it is not.  For example, in my data warehouse I have an enrollment record active from 1/1/2010 through 12/31/2010 (12 months).  If the user runs an excel cube report for 2010 by months, I want to show a 1 for all 12 months (if the report were run for quarters, then a 1 for each quarter etc.).  If the user runs a report from 2008 through 2010, the record would show 0 for 2008 and 2009.


Now I could haul off and create a daily record for that enrollment, and have 365 daily records in my DB.  I really don't want to do that. Conceptually here's what I want to accomplish:


IIF([Coverage Start Date].[Date].CurrentMember.FirstChild>=[Coverage Start Date].[Calendar Date] AND [Coverage Start Date].[Date].CurrentMember.FirstChild <= [Coverage End Date].[Calendar Date],1,0)


So if the current member in the example above is Q3 2010, then [Coverage Start Date].[Date].CurrentMember.FirstChild would be '7/1/2010' (first day of Q3).  Since 7/1 is >= 1/1 [Coverage Start Date].[Calendar Date] and <= 12/31 [Coverage End Date].[Calendar Date] this should evaluate to 1.


it doesn't. I ge

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