How to compare two dimension values in a filter
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?