Filtered Measures (Tuple Measures)

Filtered Measures (Tuple Measures)

A normal measure considers all records in the fact table for which the source value is not null. In some cases, you may want to define a filtered measure, which has the following behavior:

• The measure is null for certain records.

• For the other records, the measure has a value.

For a filtered measure (also informally called a tuple measure), use a value_expression like the following:

([MEASURES].[my measure],[DIMD].[HIER].[LEVEL].[member name])

In this case, value_expression is a tuple expression where:

• [MEASURES].[my measure] is the measure to use as a basis.

• [DIMD].[HIER].[LEVEL].[member name] is the member for which the measure value should be non-null.

For example, the Avg Test Score measure is the average test score considering all patients who have a non-null value for the test. Suppose that in addition to the Avg Test Score measure, your customers would like to see another column that just shows the average test scores for patients with coronary heart disease (the CHD diagnosis). That is, the customers would like to have the measure Avg Test Score - CHD. In this case, you can create a calculated measure that has the following value_expression:

(MEASURES.[avg test score],diagd.h1.diagnoses.chd)

For example:

WITH MEMBER MEASURES.[avg test score - chd] AS 
'(MEASURES.[avg test score],diagd.h1.diagnoses.chd)' 
SELECT MEASURES.[avg test score - chd] ON 0, aged.[age bucket].MEMBERS ON 1 FROM demomdx
 
                        avg test score - c
1 0 to 9                                  *
2 10 to 19                                *
3 20 to 29                                *
4 30 to 39                                *
5 40 to 49                            78.00
6 50 to 59                            75.75
7 60 to 69                            80.71
8 70 to 79                            83.33
9 80+                                 55.25