How to compare two dimension values in a filter

Question

Question

Jenna Poindexter · Dec 7, 2016

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

Peter Steiwer · Dec 7, 2016

You can definitely write a PlugIn to evaluate the two values and exclude any records from your returned count that are equal. Off the top of my head, I can not think of anything that will do this automatically or easily from MDX functions

0 0

Peter Steiwer · Dec 7, 2016

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.

0 0

score 1 · Answer 1 · 2016-12-07T15:12:15-05:00

Peter-

I can think of two ways, one using the plugin and a second by just adding a new dimension to the cube that compared these two values. Would there be any benefit to doing it either way? I would think less coding in the case of adding a dimension to the cube.

score 0 · Answer 2 · 2016-12-07T15:41:41-05:00

Evgeny Shvarov · Dec 7, 2016

Hi, Kenneth!

Maybe IIF can help somehow here?

0 0