Create advanced filters

Advanced filters are expression-based filters that can be used to create a custom filter at the dataset or card level, or applied to data as part of a data engineering workflow. Advanced filters include access to the same operators (for example, +, ÷, >, and <>) and functions as calculated fields.

Examples

Advanced filters can be useful in situations in which the query is complicated, or if some of the information is unknown or needs to be calculated. The following are examples of how you can use advanced filters:

• You have a dataset with schools across the United States, but you want to perform analysis on schools in Dallas County, Texas. A regular filter for Dallas County returns schools in Alabama, Iowa, and Texas. An advanced filter using the expression County="Dallas" AND State="Texas" will filter the dataset so that only schools in Dallas County, Texas, are returned.
• You have a dataset with store locations, revenue, and expenses, but you want to see only the stores that are losing money. An advanced filter using the expression Revenue<Expenses will filter the dataset so that only stores with revenue less than expenses are returned.
• You have a dataset with hydrant locations, inspection dates, and inspection status over several years, but you only want to see the inspections for damaged hydrants that occurred in 2018 and after. An advanced filter using the expression InspectionDate>=DATE(2018,01,01)AND Status="Damaged" will filter the dataset so that only the damaged hydrants assessments occurring after January 1, 2018, are shown.

  Note:

  A date function, such as DATE(), DATEVALUE(), or NOW(), must be used in an advanced filter expression using a date/time field. A date entered as a string value (for example, "01/01/2018" for January 1, 2018) will not be read as a date value and will not return results.

Apply an advanced filter at the dataset level

Complete the following steps to apply an advanced filter at the dataset level:

1. Find the dataset you want to apply the filter to in the data pane.
2. Click the Dataset options button next to the dataset.
3. On the dataset options menu, click Advanced filter.
4. Click inside the Custom filter expression box to display the available fields and functions, and begin writing your expression.
5. At the bottom of the Custom filter expression box, look for the green check mark indicating that the expression is valid.

   Note:

   Your expression must include a conditional operator, such as <, =, or <>, to be valid for filtering (for example, Revenue-Expenses>100000).

6. Click Apply.

The expression is applied to the entire dataset. Any cards created using the dataset are refreshed and updated.

Apply an advanced filter at the card level

Complete the following steps to apply an advanced filter at the card level:

1. Create a map, chart, or table using the dataset you want to filter.
2. Click the Card filter button on the card's toolbar.
3. In the New filter pane, click Advanced.
4. Click inside the Custom filter expression box to display the available fields and functions, and begin writing your expression.
5. At the bottom of the Custom filter expression box, look for the green check mark indicating that the expression is valid.

   Note:

   Your expression must include a conditional operator, such as <, =, or <>, to be valid for filtering (for example, Revenue-Expenses>100000).

6. Click Apply.

The expression is applied only to the card being filtered. Other cards that use the same dataset are not updated.

Note:

The active Card filter button displays the number of filters on the card, including advanced filters. When an advanced card filter is created, a result dataset will also be added to the data pane with the same filter applied to it at the dataset level. If new cards are created using the result dataset, all of the cards will reference the dataset filter on the result dataset and the card filter will be removed from the original card. Since the original card also references the result dataset, there will be no change to the data being displayed.

Remove or update an advanced filter

You can remove or update advanced dataset filters and advanced card filters.

Update an advanced dataset filter

Complete the following steps to update an advanced dataset filter:

1. Click the Dataset options button for the dataset that has an advanced filter applied.
2. Click Advanced filter in the dataset options menu.
3. Do one of the following:
   • Change the filter expression and click Apply to update the filter.
   • Click the Remove filter button to delete the filter.

Update an advanced card filter

Complete the following steps to update an advanced card filter:

1. Click the active Card filter button .
2. Click the filter that you want to update.
3. Do one of the following:
   • Change the filter values and click Apply to update the filter.
   • Click the Remove filter button to delete the filter.

Apply an advanced filter using data engineering

Note:

Data engineering is available in Insights desktop. All Insights in ArcGIS Online and Insights in ArcGIS Enterprise users have access to Insights desktop. For more information, see Introduction to ArcGIS Insights.

Data engineering is currently in Preview.

Advanced filters can be applied as either an import tool or a dataset tool.

Import tool

Advanced filters and other import tools run while adding a dataset are applied to the sample and output dataset but are not added to the data model.

Complete the following steps to apply the advanced filter import tool:

1. Open the Add to page window by doing one of the following:
   • Create a data workbook. The Add to page window appears when the data workbook is created.
   • Click the Add to page button above the data pane in an existing data workbook.
2. Select a dataset to add to the data workbook.
3. Click the Import options button .
4. Choose Filter dataset from the menu.

   The Selected columns window appears with a preview of the data.

5. Click Advanced.
6. Type and use the Function and Columns menus to write an expression in the Custom filter expression box.
7. At the bottom of the Custom filter expression box, look for the green check mark indicating that the expression is valid.

   Note:

   Your expression must include a conditional operator, such as <, =, or <>, to be valid for filtering (for example, Revenue-Expenses>100000).

8. Click Apply.
9. Click Finish.

The filter is applied. You can choose additional datasets or click Add to add the selected datasets to the workbook.

Dataset tool

Advanced filters run as a dataset tool are added automatically to the data model.

Complete the following steps to apply the advanced filter dataset tool:

1. Open a data workbook in Insights desktop and add data if necessary.
2. On the side toolbar, click the Advanced filter button .
3. Type and use the Function and Columns menus to write an expression in the Custom filter expression box.

   Note:

   If the data workbook has more than one dataset, the input options are based on the active table.

4. At the bottom of the Custom filter expression box, look for the green check mark indicating that the expression is valid.

   Note:

   Your expression must include a conditional operator, such as <, =, or <>, to be valid for filtering (for example, Revenue-Expenses>100000).

5. Click Apply.

The filter is added to the data model. The data preview updates to show the filtered data.

Expression examples

The following table provides examples of the types of expressions that can be used in advanced filters:

Expression	Description
DATEDIF(date_field, NOW(), "D")<=28	Filters the data to entries from the past 28 days.
number_field>AVG(number_field)	Filters the data to rows where the value of number_field is greater than average.
number_field_1>number_field_2	Filters the data to rows where the value of number_field_1 is greater than the value of number_field_2.
number_field<10 OR number_field>20	Filters the data so that the middle values (in this case, numbers between 10 and 20) are not included.
string_field IS NOT NULL	Filters the data to only include rows where the field contains nonnull values.

Limitations

Advanced filters are not available for some remote feature layers. If your remote feature layer does not support advanced filters, you can copy the layer to your workbook and apply an advanced filter to the copy.

Advanced filter expressions may appear incorrect when using mixed left-to-right and right-to-left characters within the same expression. For example, an expression using field names in both English and Arabic may not display properly. The results of the expression are not affected by the mixed left-to-right and right-to-left characters.