setting a measure value to NULL if a particular dimension is used in excel

Question

I have an SSAS-2014 cube. I want to set a particular measure to NULL if a particular dimension is being used in either axis or the filter pane of a pivot table in excel. Now, the most intuitive solution, is to scope this measure to not work with the members of that dimension. Say, I don't want the measure to work with accounting period members, then I use the following MDX in the cube:

CREATE MEMBER CURRENTCUBE.[Measures].[Test Measure] AS 1;

SCOPE([Measures].[Test Measure]);
SCOPE(DESCENDANTS([DIM Accounting Period].[Accounting Period Hierarchy].[All],,AFTER));
THIS = NULL;
END SCOPE;
END SCOPE;

This seems to work if I select one year in the filter of the pivot table.

This seems good since excel send the following MDX back to the SSAS engine

SELECT NON EMPTY Hierarchize (
 {
  DrilldownLevel (
   { [DIM Production Period].[Production Month].[All Dates] }
   ,
   ,
   , INCLUDE_CALC_MEMBERS
  )
 }
)  ON COLUMNS
FROM [CUBE - Opex Analysis]
WHERE ( [DIM Accounting Period].[Accounting Period Hierarchy].[Accounting Year].[2015],
        [Measures].[Test Measure] ) CELL PROPERTIES VALUE
, FORMAT_STRING
, LANGUAGE
, BACK_COLOR
, FORE_COLOR
, FONT_FLAGS

Problem happens if I select two members in the filter pane, as shown in this image

and the reason for that seems to be related to the MDX that excel sends back to the engine. It encapsulate the items inside a subcube making the engine thinks that no accounting year is being selected. This is the MDX that excel uses:

SELECT NON EMPTY Hierarchize (
 {
  DrilldownLevel (
   { [DIM Production Period].[Production Month].[All Dates] }
   ,
   ,
   , INCLUDE_CALC_MEMBERS
  )
 }
)  ON COLUMNS
FROM (
 SELECT (
 { [DIM Accounting Period].[Accounting Period Hierarchy].[Accounting Year].[2015],
   [DIM Accounting Period].[Accounting Period Hierarchy].[Accounting Year].[2016] } ) ON COLUMNS
 FROM [CUBE - Opex Analysis]
)
WHERE ( [Measures].[Test Measure] ) CELL PROPERTIES VALUE
, FORMAT_STRING
, LANGUAGE
, BACK_COLOR
, FORE_COLOR
, FONT_FLAGS

Please note, That I also tried to use EXISTING trying to capture accounting years being used but I was having the same problem. Does anyone know a way to fix this?. I had a similar problem before, but implementing the solution I used here is overkill for performance Similar Question I am using SSAS 2014 and Excel 2013

Ferdipux Ferdipux · Accepted Answer · 2016-06-30T18:59:57

Hm, the only solution I could envisage for subcube scenario is to create dynamic set on probed dimension as described in the article of referred question. Then for your scope add another sub scope([DIM Accounting Period].[Accounting Period Hierarchy].[All]) and count DESCENDANTS([DIM Accounting Period].[Accounting Period Hierarchy].[All],,AFTER) in this scope. Compare it with count of the dynamic set. If two do match - you are not inside subcube from subselect, otherwise you are.

setting a measure value to NULL if a particular dimension is used in excel

1 Answers