Is there a simple way to achieve this using MDX?

Hi all,

I want to simply take a value that is returned in my fact table for a
measure and copy is across all cells for that measure. (Analysis services 2000)

Current result

Date Measure
1/1/06 0
2/1/06 0
3/1/06 100 -- there will only ever be 1 value for any day. Everything else empty/0
4/1/06 0

Desired result

Date Measure
1/1/06 100
2/1/06 100
3/1/06 100 -- just copy this value to all cells and allow it to
roll up to 400 when drilled up.
4/1/06 100

Regards
ImmyHere's one idea - assuming you're using AS 2000 Enterprise Edition, suppose you create both "sum" and "max" measures from the fact table measure, like SumMeas and MaxMeas. Then you could create a calculated cell for SumMeas only at the daily (leaf) level of the Date dimension, which overwrites values with something like ([Measures].[MaxMeas], [Date].[All]). Then, these values should get summed at higher levels of the Date hierarchy.|||

Hi Deeps,

I'm not using Ent Edt. Do you know if there are any other options available to me?

Or maybe I can deal with it server side instead, but i know it will slow down my cubing process if i have to come up with a server side (data source) solution.

Immy

|||Can you create a view for your fact table, which adds a field for the maximum value across all records with the same dimension values other than date? This field could be used for the "sum" measure.