November 15, 2011 at 7:03 am
Do you guys have a general idea on how (or if it's possible) to filter measures on the intersections of a many to many dimension?
Like the way this cube is setup, there is 3 tables: Person, Answer, and PersonAnswerBridge. Answer represents every possible checkbox on the survey, person represents the person taking the test, and the bridge table represents what answers they selected.
I'm new to MDX and I'm still learning but it seems all the strategies that I've read about so far does not apply to many to many relationships. Does anyone know or have a general direction I should look towards? Basically I'd like to do something where we count the number of people that selected anything in the Answer set {1,2,3,4,5} but also selected something in the set {10,12,15,18}, where all these numbers are in the Answer table (using numbers just for the example). I thought intersect would be ideal, but it doesn't work. It only works when the sets overlap.
June 4, 2012 at 2:02 am
Did you have any response to your question? I'm having the same issue...
June 5, 2012 at 6:43 am
Instead of intersecting the answer sets, I would suggest intersecting the people who had those answers. So you would need to construct two MDX set statements that use the answers as a filter and return the people.
That should give you what you are after.
June 15, 2012 at 8:29 am
You've set up your question as a SQL problem. MDX solutions require a little fine-tuning and are not quite so straightforward as SQL solutions.
So without knowing too much details about your set-up this is what you may attempt to do.
==> Set up 2 indicators (attributes) in your person dimension e.g ExistInSet1 as true/false where a person belongs to the true set if he answers in set {1,2,3,4,5}
==> Do the same for the set2
==> Now you can use your mdx to get a count by adding a WHERE clause ([persons].[ExistInSet1].&[True],[persons].[ExistInSet2].&[True] } to your mdx
This will return only people that exist in both sets thereby giving you the necessary counts for the measures you use. 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply