dan english


All right, I have been trying to come up with a solution for this problem, but so far I have had no luck. Maybe someone else can point me in the right direction:

I am working with some results from surveys and each respondents has a weight value related to them for each of their responses to the questions. So if respondent #1 has a weight value of 1.5 and respondent #2 has a weight value of 2 and respondent #3 has a weight value of 1.75 and they respond to the following questions like such:

Respondet# Question Response Weight

1 Drink Light Beer Yes 1.5

1 Drink Regular Beer Yes 1.5

2 Drink Light Beer Yes 2.0

2 Drink Regular Beer No 2.0

3 Drink Light Beer No 1.75

3 Drink Regular Beer No 1.75

My over all calculation is to take the Weight value for the respondents that responded with ¡®Yes¡¯ and divide by the Weight value of all of the respondents. The gotcha is that if the respondent selected ¡®Yes¡¯ to more than one Question then I want to only include them once in the numerator.

What I am getting is: (1.5+1.5+2.0)/(1.5+2.0+1.75) = .95238

What I want to get is: (1.5+2.0)/(1.5+2.0+1.75) = .66667

As of right now I do not know how I can filter out the first respondents second response of ¡®Yes¡¯.

Right now my MDX looks like the following:

([Beverage].[Report Response Text].&[1], Measures.[Weight Value])/ iif(count(descendants(beverage.[alcohol].currentmember,,after))>0,Measures.[Weight Value]/count(descendants(beverage.[alcohol].currentmember,,after)), Measures.[Weight Value])

The denominator in this query is basically determining if there are multiple children associated with the Alcohol Type (which in this case would be Beer and the children would be Light Beer and Regular Beer). I am then taking the Weight and dividing it by the number of children associated with the Alcohol Type (which in this case would be (1.5+1.5+2.0+2.0+1.75+1.75)/2 = 5.25). The Report Response Text value of 1 = Yes and 2 = No.

Does anyone have any suggestions on how I can filter out the second ¡®Yes¡¯ response for Respondent #1




Re: MDX Calculation Question - need to filter out a response value in overall calc

Deepak Puri


How about trying to use the many-to-many dimension feature of AS 2005: assuming that you have a [Respondent] dimension table, with a [Weight] field, one approach would be to add a "Respondent" Measure Group on this table, with a "sum" measure like [RespWeight] on the [Weight] field. The [Respondent] dimension would then have a "fact" relation to this "Respondent" Measure Group, and a regular relation to the survey fact table/Measure Group (which has RespondentID, Question and Response fields). And assuming there is a [Question_Response] dimension, with separate Question and Response attributes, this [Question_Response] dimension can be directly related to the survey fact table via a {Question, Response} composite key.

Now the [Question_Response] dimension can have a many-to-many relation to the "Respondent" Measure Group via the survey Intermediate Measure Group, and the calculation of weighted "Yes" responses could be like:

([Measures].[[RespWeight], [Question_Response].[Response].[Yes])

/ ([Measures].[[RespWeight], [Question_Response].[Response].[All])







Re: MDX Calculation Question - need to filter out a response value in overall calc

dan english

This completely makes sense and I just over looked this relationship in my dimension usage. I had everything in place except for the relationship from the question_response dimension to the respondent fact measure group. Thanks so much.