FILTER - DeepSee MDX Reference - Caché & Ensemble 2018.1

docs.intersystems.com

DeepSee MDX Reference

FILTER

InterSystems: The power behind what matters

InterSystems DeepSee > DeepSee MDX Reference > MDX Functions > FILTER

Examines a set and returns the subset in which the given expression is true for each element. The set order is unchanged.

Returned Type

This function returns a set.

Syntax and Details

FILTER(set_expression, logical_expression)

set_expression is an expression that evaluates to a set.
logical_expression is a logical expression, typically that examines a measure value or a property value.

Instead of logical_expression, you can use a measure search expression; see the example to see the behavior of FILTER in this case.

Example

For example, consider the following query, which returns only cities with more than 1150 patients.

SELECT MEASURES.[%COUNT] ON 0, 
FILTER(homed.city.MEMBERS, MEASURES.[%COUNT]>1150) ON 1 FROM patients
                             Patient Count
1 Cedar Falls                         1,188
2 Centerville                         1,155
3 Cypress                             1,221
4 Elm Heights                         1,266
5 Juniper                             1,197
6 Magnolia                            1,156

In comparison, consider the following query, which returns cities with any patient count:

SELECT MEASURES.[%COUNT] ON 0, 
FILTER(homed.city.MEMBERS, MEASURES.[%COUNT]>=0) ON 1 FROM patients

                             Patient Count
1 Cedar Falls                         1,188
2 Centerville                         1,155
3 Cypress                             1,221
4 Elm Heights                         1,266
5 Juniper                             1,197
6 Magnolia                            1,156
7 Pine                                1,139
8 Redwood                             1,144
9 Spruce                              1,135

For another example, the following query uses a more complex filter expression:

SELECT MEASURES.[%COUNT] ON 0, 
FILTER(diagd.members, (MEASURES.[%COUNT]>500 and MEASURES.[%COUNT]<1000)) ON 1 
FROM patients

                             Patient Count
1 asthma                                746
2 diabetes                              555

The next example uses a filter expression that evaluates a property:

SELECT homed.CURRENTMEMBER.PROPERTIES("Population") ON 0, 
FILTER(homed.city.MEMBERS,homed.CURRENTMEMBER.PROPERTIES("Population")>20000) ON 1 FROM patients

                                       ZIP
1 Cedar Falls                        90,000
2 Centerville                        49,000
3 Elm Heights                        33,194
4 Redwood                            29,192

If you use FILTER with a measure search expression, the function returns only those members that are based on at least one fact that meets the given criteria. (A measure search expression is an InterSystems extension to MDX that considers the measure values in the fact table itself.)

SELECT {MEASURES.[%COUNT]} ON 0, FILTER(diagd.members, %SEARCH.&[[MEASURES].[age]<10]) ON 1 FROM patients

                             Patient Count
1 None                                8,425
2 asthma                                703

This query shows the diagnoses that have at least one patient under ten years old. For other diagnoses such as diabetes and osteoporosis, there are no such young patients.