Hi,

I'm relatively new to MDX and found myself struggling a bit today to work out how to produce a calculated measure that is a filtered version of one of the base measures. In my case, the base measure was named [Gross Premium] and I wanted a calculated measure named [Written Gross Premium]. I managed to write the following MDX query, which gave me the value I wanted thanks to the WHERE clause. However, I wanted to provide a calculated measure in the cube named [Written Gross Premium] to avoid the user from needing to remember to filter [Gross Premium] appropriately by Record Status and Transaction Type. I did work out a solution after a frustrating couple of hours (details below) but I'd really appreciate some insight from a more experienced MDX developer to comment on whether or not the solution is the only way, and if not is there a better way?

Firstly, here's my initial MDX query to return me all the Written Gross Premium for Dec-2014:

SELECT [Measures].[Gross Premium] ON COlUMNS,
[Transaction Date].[Calendar Month Hierarchy].[Calendar Year Month].&[2014-12] ON ROWS
FROM [MyCube]
WHERE ([Policy].[Record Status].&[Is Active],
FILTER([Transaction Type].[Transaction Hierarchy].[Transaction Type],
[Transaction Type].[Transaction Hierarchy].currentmember IS
[Transaction Type].[Transaction Hierarchy].[Transaction Type].&[Written Additional Premium]
OR [Transaction Type].[Transaction Hierarchy].currentmember IS
[Transaction Type].[Transaction Hierarchy].[Transaction Type].&[Written Premium]
OR [Transaction Type].[Transaction Hierarchy].currentmember IS
[Transaction Type].[Transaction Hierarchy].[Transaction Type].&[Written Return Premium]));

It took me a while as I fiddled about unsuccessfully trying to figure out how to crowbar the FILTER function into a query scoped calculated measure, but eventually I realised that I could break down the calculated measure definition into multiple steps, which led me to the query below. This query works and produces the number I want but is it the best way to solve the problem?

WITH MEMBER [Measures].[Written Gross Premium Only] AS
([Measures].[Gross Premium], [Policy].[Record Status].&[Is Active], [Transaction Type].[Transaction Hierarchy].[Transaction Type].&[Written Premium])
MEMBER [Measures].[Written Gross Additional Premium] AS
([Measures].[Gross Premium], [Policy].[Record Status].&[Is Active], [Transaction Type].[Transaction Hierarchy].[Transaction Type].&[Written Additional Premium])
MEMBER [Measures].[Written Gross Return Premium] AS
([Measures].[Gross Premium], [Policy].[Record Status].&[Is Active], [Transaction Type].[Transaction Hierarchy].[Transaction Type].&[Written Return Premium])
MEMBER [Measures].[Written Gross Premium] AS
[Measures].[Written Gross Premium Only] + [Measures].[Written Gross Additional Premium] + [Measures].[Written Gross Return Premium]
SELECT [Measures].[Written Gross Premium] ON COLUMNS,
[Transaction Date].[Calendar Month Hierarchy].[Calendar Year Month].&[2014-12] ON ROWS
FROM [MyCube];

I also figured out that I could add a named calculation ([Written Premium Flag], which holds Y or N) to the [Transaction Type] dimension, and this enabled me to deliver [Measures].[Written Gross Premium] without the need for the three intermediate calculated measures. The following query also worked (after the SSAS database was re-deployed and fully processed):

WITH MEMBER [Measures].[Written Gross Premium] AS
([Measures].[Gross Premium],
[Transaction Type].[Written Premium Flag].&[Y],
[Policy].[Record Status].&[Is Active])
SELECT {[Measures].[Gross Premium], [Measures].[Written Gross Premium]} ON COLUMNS,
[Transaction Date].[Calendar Month Hierarchy].[Calendar Year Month].&[2014-12] ON ROWS
FROM [MyCube];

Does anyone have any thoughts on the above solutions to this problem and what is considered general best practice to approach this type of thing? I'm sure it must be common to provide calculated measures that are appropriately filtered versions of base measures but any insights into the issue of needing to specify multiple logical OR conditions (or any combination of logical conditions for that matter) will be much appreciated.

Many thanks.