DeepSee MDX Reference
FILTER
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

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 a DeepSee 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.
See Also