# 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)
```

## 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.