## How to Create a Filtered Calculated Measure

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:

[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
OR [Transaction Type].[Transaction Hierarchy].currentmember IS
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
MEMBER [Measures].[Written Gross Return Premium] AS
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