FILTER - DeepSee MDX Reference - Caché & Ensemble 2018.1

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

FILTER(set_expression, logical_expression)

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

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.