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