Getting something out of nothing

I was working on a cube that where I wanted values visible for some cells and all other cells to be null. I wanted to define a subcube that would have visible values and apply any needed MDX. Then I would define the complement of this subcube and set the cell values to null. The problem was that while the subcube where I wanted values was easy to define, the complement of that subcube was very difficult to define

I next tried to create a calculated member that would be null and then use a SCOPE statement and an assignment statement to make the calculated member have values where I wanted to see data. The problem with this is that you can only use assignment statements to assign values on the physical space of the cube. You can not assign a calculated member a new value.

My solution was to create a new measure group and measure. In the measure group SQL statement I had “WHERE 1=2”. This means that the measure group will always be empty. But because the measure is a physical part of the cube, I can use a SCOPE statement and an assignment statement to apply MDX to the cells that I want to have values, setting the measure in the empty measure group equal to the measure in the measure group that contains data. The cells that I want null are already null in this new measure group. The final step is the set Visible=False in my original measure.

Scott