.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

MDX count distinct calculation where another column

Posted By:      Posted Date: September 25, 2010    Points: 0   Category :Sql Server

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

View Complete Post

More Related Resource Links

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,

How to get character count in 'Multiple Text 'column using Calculated Column?


Hi All,

I have a multiple text box in a list

I want

  • A column which lists number of characters in multiple list column example 188, 199 etc
  • Multiple character column must not have more than 200 chars
Is this possible?

Appreciate all the help!


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"  

Use DISTINCT or a variant of it with more than one column?


Hi all,

for the project I am working on, I have quite a special requirement. I need to produce a listing of products from a table of products, but featuring the lowest price.

Let me elaborate.

The Products table is similar to this:

ID Product Category Supplier Price
1 Tomato Large DELCO $10
2 Tomato Large DELCO $20
3 Tomato Large SALCO $30
4 Potato Large MILCO $10
5 Potato Large SALCO $20

Now, upon request, I need to present a listing of products [namely, their ID's] that contains ONLY THE PRODUCTS WITH THE LOWEST PRICE. Like this:

ID <

"How to get distinct values of sharepoint column using SSRS"


    I have integrated sharepoint list data to SQL Server reporting services. I am using the below to query sharepoint list data using sql reporting services.

   <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
         <Parameter Name="listName">
            <DefaultValue>{GUID of list}</DefaultValue>
         <Parameter Name="viewName">
            <DefaultValue>{GUID of listview}</DefaultValue>
         <Parameter Name="rowLimit">
<ElementPath IgnoreNamespaces="True">*</ElementPath>

By using this query, I am getting a dataset which includes all the columns of sharepoint list. Among these columns, I wanted to display only 2 columns (i.e Region and Sales type) using chart. I have created a Region parameter but when I click

How to Compare Text Column with Today Calculation in view?


I  have 2 columns

Birth Date (DateTime), Birth-Month(Text- Calculated from Birth Date with MONTH([Birth Date]))

Now I want to know who's birthday in this running month?

So How can I implement filter or CAML Query for a view.


Regards, Kashyap Patel http://interview-question-aspnet.blogspot.com/

Distinct Result Set Column level


Hi ...

How can I do this: if Col2 = Col1 then update Col2 with Col3 only when col3 is not equal to Col1, if equal then update col2 with Col 4 only when Col4 is not equal to Col1, If equal update with NULL.


I can write a CASE statement but its getting uglier becoz of about 10-12 colunm checks. any better solution for doing this.








@table TABLE(ID INT,Col1

How to get distinct values of sharepoint column using SSRS: paramter Greyed out


I'm referring to a previous thread: How to get distinct values of SharePoint column using SSRS.  I followed all the steps as set out but when I view my report, the parameter is greyed out.  I then downloaded the example report of Jin Chen.  In that report, he suggests a blank parameter label for the main parameter, as well as, a default value for the hidden parameter (which is different from the initial thread posted).  When I change my report accordingly, I get an error: An error occurred during local report processing. MainParameter.  Anyone with suggestions?

How to add another column to sum/average/count/or any possible calculations to those pivoted columns


Hi All,

I am currently using this Dynamic Pivot Table Queries from this link:


I manage to get the result i required but i need to add further column to it. I have a table such as this.

Name  |   Item   |   Points

Eddy       A             3

Eddy       B             4

John       A             2


I could get the result such as this from the above link after pivot.

Name  |  A   |    B

Eddy      3         4

John       2         null

How do i make it to become like this? Adding a column average so the final result will look like this.

Name | Average |  A  |  B

Eddy     3.5           3      4

John        1          

SSIS audit column Get date and total count of row



I have one task in which i have to transfer data from sql to flat file  also i need one more flat file in which i have Number of Row and Getdate

I use multicast column  and then agreegate to count row  i do not know How to achive getdate in this.



Column to show count of attachments uploaded for each List item in MOSS 2007


We have two requirements:

First, we need a column which shows the count of attachments that are for uploaded each item, the list structure is like below:

No. Of Files Attached Title Category Description Owner

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

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