Force calculated member to entire cube

Question

I'm trying to add a calculated member to my cube, which will return the first fiscal year where there is any data at all in a particular measure.

The purpose is to suppress (i.e. NULLify) various year-on-year calculated measures when the year is this first year: in that year, comparison with the previous year is meaningless.

I've got this so far:

WITH MEMBER Measures.DataStartYear_Sales
AS
HEAD(
    NONEMPTY([Calendar].[Fiscal Periods].[Fiscal Year].Members,[Measures].[QuantityOrdered])
    ,1).Item(0).Properties("NAME")

At the moment:

a. It's a query-scoped measure, as that's easier to experiment with.

b. It returns the first year's Name, as that's easier to see. Eventually I'll just return the member itself, and do an IS comparison against the year hierarchy .CurrentMember in the other calculated member calculations.

The problem I expected, which has happened, is that I only want this measure to be calculated once, over the whole cube. But when I used it in a query, it obviously reacts to the context. For example, if I stick the Products dimension on ROWS, the value of this measure will be different for each row, because each product's earliest order date is different.

That is of course useful, but it's not what I want. Is there some way to force this measure to ignore the query context, and always return the same value?

I looked into SCOPE_ISOLATION and SOLVE_ORDER, but they don't do what I'm trying to do here.

I suppose I could specify a tuple of Dimension1.All, Dimension2.All.... DimensionN.All, covering all dimensions in the cube, but that seems messy and fragile.

The Dumb Radish The Dumb Radish · Accepted Answer · 2017-06-30T10:32:55

I think you might be able to accomplish this with static sets. Here is an example using Adventure Works that produces the same first year regardless of context:

WITH STATIC SET FirstYear AS
HEAD
    (
    NONEMPTY([Date].[Calendar Year].[Calendar Year].MEMBERS, [Measures].[Internet Sales Amount])
,   1
    )
    MEMBER FirstYearName AS
    FirstYear.ITEM(0).NAME
 SELECT
[Measures].[FirstYearName] ON COLUMNS
 ,  [Date].[Calendar Year].[Calendar Year].MEMBERS 
    //Add as many dimensions as you like here...for example
*    [Product].[Product].[Product].MEMBERS
 ON ROWS
 FROM
[Adventure Works]
;

Example output:

That should hopefully put you on the right track.

Force calculated member to entire cube

1 Answers