MDX And-criteria in Where

Last Post 27 Nov 2008 11:30 PM by zigge. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
zigge
New Member
New Member

--
27 Nov 2008 01:30 AM
Hi!

I have a case where the data being analyzed is from an online survey.
I have created three CubeDimensions; Question, BreakdownQuestion and FilterQuestion
which are cubedimensions of the database dimension Question.

These three are many-to-many joined to the RespondentFact measure group to be able
to show questions on one row, break down the data on the same questions on the other row
and filter on some of the questions.

This is all working great, I can show questions on rows, break them down over the distribution
of for ex. what gender on columns, and filter the underlying data on respondents who
live in a certain city.

My problem is that I want to filter using AND on several Questions.
For Ex. Filter on Respondents who on one question have answered that they are a Male, on another
that they live in Canada, and on a third that they are dissatisfied.
(Male AND Canada AND Dissadisfied)


My solution allows this but it results in OR criteria because the filter is a set containing all combinations.
(Male OR Canada OR Dissatisfied)

I have tried subcubes, subselects, intersections etc but it all gives the same result

Example query of what I need to accomplish:

Select
[BreakdownQuestion].[Group - Question - AnswerOption].[Question].&[Gender].Children
On Columns,
[Question].[Group - Question - AnswerOption].Children on Rows
From [577]
Where
[FilterQuestion].[Group - Question - AnswerOption].[Answer Option].&[Male]
AND
[FilterQuestion].[Group - Question - AnswerOption].[Answer Option].&[Canada]
AND
[FilterQuestion].[Group - Question - AnswerOption].[Answer Option].&[Dissatisfied]



Regards Anders
zigge
New Member
New Member

--
27 Nov 2008 11:30 PM
I found one solution to my problem but it does not feel like the best solution from a performance point of view,
because it works on row level?

Is this a good solution or are there any better?
RoundFact is the fact/dimension for one respondent (respondnet count)

With member [condition1] as
iif(([RoundFact].[RoundFact].CurrentMember, [FilterQuestion].[Group - Question - AnswerOption].[Answer Option].&[82690], [Measures].[RespondentCount]) > 0, 1, 0)
member [condition2] as
iif(([RoundFact].[RoundFact].CurrentMember, [FilterQuestion].[Group - Question - AnswerOption].[Answer Option].&[82917], [Measures].[RespondentCount]) > 0, 1, 0)
member [condition3] as
iif(([RoundFact].[RoundFact].CurrentMember, [FilterQuestion].[Group - Question - AnswerOption].[Answer Option].&[82576], [Measures].[RespondentCount]) > 0, 1, 0)
member [matchingRespondents] as
iif([condition1] = 1 AND [condition2] = 1 AND [condition3] = 1, 1, 0)
select
([BreakdownQuestion].[Group - Question - AnswerOption].[Question].&[11339].CHildren,
[Measures].[Index])
on columns
, [Question].[Group - Question - AnswerOption].Children on rows
from [577]
where Filter([RoundFact].[RoundFact].[RoundFact].members, [matchingRespondents] > 0)










Acceptable Use Policy
---