I need a measure that ignores all of my dimensions except one

[ToddAnderson]

I have a cube that has a bunch of dimensions. I also have a bunch of measures that all work fine with all of the dimensions. I now want to create a measure that only cares about one of the dimensions (my Date dimension) and ignores the rest.

I've tried this several different ways but it only works when all of the other dimensions have their All member chosen. If that is the case I can navigate the Date dimension and the measure works fine but as soon as I drill down on one of the other measures I get empty cells.

Is there some way to tell this measure to always use the All member of the dimensions that I want it to ignore? Or is there some other way to do this?

 

[ToddAnderson]

Maybe some more info would help.

I am trying to create two Average Unit Sales measures.

The first one is over stores that actively carried the item(s) in question. I have a field with a unique StoreMonth value in the fact table so I just do a Distinct Count on that data and divide the Unit Sales by this number. This works great no matter how you slice and dice the data.

The second one is supposed to be over all of the stores in our database during the time in question regardless of whether they actively carried the item(s). So, I need the Distinct Count again but this time only caring about the Date dimension (not any of the others such as Product Category).

If I setup the first scenario above with all dimensions set to their All member and then navigate through the Date dimension it works like I need my second measure to work. I need to see these side by side though and it needs to work no matter how the user slices and dices the data.

Hope that clarifies rather than confuses.

 

[MDXer]

you can't ignore any dimension that is built into a cube. The structure of the cube makes it this way. If you do not reference a dimension or any of it's members then it is assumed the dimension should be utilized at the ALL level, or the default member in a flat dimension. You can use calculated cells to only have calculations take place if a set of criteria are found to be true or you are working at a set level in a dimension.

You may be better served building a cube or set of cubes to answer this specific question. You will have much better success when building a cube to answer a specific question or set of questions than if you try to get all questions answered by the same question.

One approach from just a few moments thought on the issue is that you may want to build a couple small cubes that do the distinct count the way you need each to be done. once you have the cubes that answer each of your questions bring them together into a virtual cube utilizing the common dimensions.

First cube you could do a distinct count on the actual Store_key in the fact record if this is how it is being done, where in the second cube you have a calculated member that counts the leaf members in your store dimension.

Just a thought and some rambling.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[ToddAnderson]

MDXer,

Thanks for the quick reply.

Well, your idea about using a separate cube for this particular Distinct Count is what I have been trying. I have to do this since I have the two Distinct Count measure mentioned above and you can only have one Distinct Count per cube. As you suggested I am then using a virtual cube to bring them together. The cube that I am having problems with works fine when I browse it but when I browse the virtual cube in AS or in ProClarity I get empty fields as soon as something other than the All member is chosen on any of my other dimensions.

I amalso doing your second suggestion where I count the store key. I have to do a slight variation to make it work correctly but it comes down to the same thing.

I just can't figure out why I'm getting this strange behavior. I was expecting this to be easy. Why does it always seem to be that way?

Todd