MDX measures Filtering

Question

I have a MDX query that gets Sales depending on Date, StateProduct and Country. Now I need to know how many Sales are in One particular country and how many in the rest. Is it possible to filter Sales measure into two differents measures? Like in the example:

MEMBER [Measures].[SalesOneParticular] AS Filter Sales measure in One particular country.

MEMBER [Measures].[SalesOthers] AS Filter Sales measure in Other countries

SELECT {[Measures].[SalesOneParticular],[Measures].[SalesOthers]} ON COLUMNS

Thanks in advance.

Rick Rick · Accepted Answer · 2012-12-13T13:13:39

The first member is just a normal slice for the specific country you want. The second member is all the items minus the first member. These members will take into account anything you have in your slicer.

WITH MEMBER [Measures].[SalesOneParticular] AS
([Measures].[Sales], [Country].[Country].&[USA])

MEMBER [Measures].[SalesOthers] AS
([Measures].[Sales], [Country].[Country]) - [Measures].[SalesOneParticular])

SELECT {[Measures].[SalesOneParticular],[Measures].[SalesOthers]} ON COLUMNS

For [SalesOthers] you could also do

MEMBER [Measurs].[SalesOthers] AS
[Measures].[Sales] - Measures].[SalesOneParticular]

For this example it assumes your comparator is the top of the hierarchy and would be faster. If you were wanting to compare something else in the hierarchy , use the first example.

MDX measures Filtering

1 Answers