Question
· Dec 7, 2016

How to compare two dimension values in a filter

Hi-

I am trying to figure out how to compare two dimension values in a MDX query such that records where two dimension values are the same will be filtered out.

To test this, I have modified the HoleFoods cube in SAMPLES namespace to have a new dimension called RandomRegion

I want to create a MDX query where the [Outlet].[H1].[Region] value is not equal to the [RandomRegion].[H1].[RandomRegion] value.

Here's the query I need to add the filter to:

SELECT NON EMPTY HEAD(NONEMPTYCROSSJOIN([Outlet].[H1].[Region].Members,[RandomRegion].[H1].[RandomRegion].Members),2000,SAMPLE) ON 1 FROM [HOLEFOODS]

For example, if the Region = Asia and the RandomRegion = Asia I dont want to show that row in my result set.

Any thoughts on how I can accomplish this?

Discussion (4)1
Log in or sign up to continue

Ah yes, creating some new dimension, for example, called "IsDifferent" and having values of 0 or 1 would definitely work. If this dimension makes sense within your cube, then it would be OK to add (for performance and simplicity reasons). If the case presents itself where you have another two dimensions you need to compare, you will need to add "IsDifferent2". This pattern may repeat based on the use case.

It would be possible to create a PlugIn that takes dimension names as parameters, which would allow you to dynamically compare values without needing to pre-define all of your combinations.