Concepts → Measure Filter

About a measure filter

A measure filter specifies a filter expression for a given measure pill in an insight. For example, as a dashboard developer, you want to show the total sales by the last three years — 2019, 2020, and 2021 — on a KPI insight. By using a measure filter, you can specify a filter expression for each measure pill, the first being for the year 2019, the next for 2020, and the last for 2021.

In the Properties panel for a measure pill, in the Filter section, you can create one or more measure filters. In the Filter panel for a measure filter pill, you can specify a filter expression.

A measure filter pill is either a data-backed column or a formula. When a column from a physical schema table or runtime business view is a measure filter pill, you specify the filter operator and a filter value within the Filter panel so as to specify the filter expression. When a measure filter pill is a formula, you can use the Formula Builder to create a formula expression or specify a filter expression session variable. A formula expression can contain Boolean logic, but must contain a filter expression for the measure filter.

Note: Visualizations that do not support a measure filter

The following visualizations does not support a measure filter:

Rich Text
Listing table For an Incorta Analyzer Table or an Incorta Analyzer View, the Analyzer only supports a measure filter for an Aggregated Table visualization.

In order to create or edit the following with the Analyzer, you must belong to a group assigned the necessary security role(s) AND be the owner of the object or have Edit access rights to the object:

an insight on a dashboard tab
an Incorta Analyzer Table in a physical schema
an Incorta Analyzer View in a business schema

The parent objects are a dashboard, physical schema, and business schema, respectively. Along with all other insight filters, a measure filter is not visible to a user with View or Share access rights to a dashboard, physical schema, or business schema. In addition, a measure filter is not visible in the Filter Values panel of the Analyzer.

Filter expression context for a measure filter

The parent of a measure filter pill is a measure pill. The parent object of a measure pill is either an insight, an Incorta Analyzer Table, or an Incorta Analyzer View. The parent object governs the filter expression context and, in doing so, determines the evaluation and application of the filter expression. The definition of a measure filter can also alter the query plan of the parent measure.

A measure filter for an Incorta Analyzer Table

An Incorta Analyzer Table is a derived table, meaning that it queries data from a physical schema or a business schema. For an Incorta Analyzer Table, the Analyzer only supports a measure filter for an Aggregated Table visualization.

A measure filter for an Incorta Analyzer View

An Incorta Analyzer View is a type of runtime business view. For an Incorta Analyzer View, the Analyzer only supports a measure filter for an Aggregated Table visualization.

A measure filter for an insight

The Analytics Service applies a measure filter in the query plan of an insight query. A query plan relates to one or more measures for the given insight. The Analytics Service evaluates the query at runtime when the insight renders on the dashboard tab.

You can create a measure filter for most visualizations that support two and three dimensions. Here are the visualizations that support a measure filter:

One dimension

Note

Although an Aggregated Table supports a one dimension configuration, this configuration is not recommended. A Listing Yable does not support a measure filter in a one dimension configuration.

Two dimensions

Three dimensions

Supported filterable columns based on the filter expression context of a measure filter

The following table details the filterable columns that you can use or reference for each context. These columns can be data-backed columns or formula columns:

Filterable columns	Context: Incorta Analyzer Table	Context: Incorta Analyzer View	Context: Insight
Business_Schema.Incorta_View.Column			✔
Business_Schema.Incorta_View.Formula_Column			✔
Business_Schema.View.Column	✔	✔	✔
Business_Schema.View.Formula_Column	✔	✔	✔
Physical_Schema.Table.Column	✔	✔	✔
Physical_Schema.Table.Formula_Column	✔	✔	✔
Physical_Schema.Incorta_Analyzer_Table.Column		✔	✔
Physical_Schema.Incorta_Analyzer_Table.Formula_Column		✔	✔
Physical_Schema.Incorta_SQL_Table.Column		✔	✔
Physical_Schema.Materialized_View.Column	✔	✔	✔
Physical_Schema.Materialized_View.Formula_Column	✔	✔	✔

The joins between physical schema tables and the query plan for the insight measure work together to determine if a measure filter uses a filterable column. For example, you can specify a physical schema column in a parent table as a measure filter for a measure in a child table. In contrast, a physical schema column from a child table is not a filterable column for a parent table as a measure filter, even when it is the foreign key reference. The behavior is the same for a formula as a measure filter. The following are the filterable columns that you can use or reference in a formula column when creating a measure filter pill:

The filterable column is a dimension or measure column, that is, it exists as pill in either the Grouping Dimension or Measure trays in the Analyzer.
The filterable column has a sibling column from the same physical schema table, and that sibling is a dimension or measure.

Important: Incorta Analyzer View

When a measure and/or dimension pills are columns or formula columns from an Incorta Analyzer View, all filterable columns must be from the Incorta Analyzer View. Similarly, if the measures and/or dimensions pills are not columns or formula columns from an Incorta Analyzer View, all filterable columns must not be from an Incorta Analyzer View.

Types of measure filters in the Analyzer

When creating a measure filter pill, you can use either a formula or a column of any data type.

When a column from a physical schema or a runtime business schema is a measure filter pill, you specify the filter operator and one or more filter values within the Filter panel in the Analyzer to complete the filter expression. Selected columns can be data-backed columns that reference data that persists to shared storage or formula columns.

Alternatively, from the Data panel, you can add a formula to the Filter property in the Properties panel of a measure. You can use the Formula Builder to create the filter expression. The context of the measure filter determines the types of columns that you can use or reference. For more information, see Supported filterable columns based on the filter expression context of the measure filter.

Measure filter pill as a Column

A measure filter pill represents a filter expression. The filterable column in the filter expression can be a key, dimension, or measure column. The filterable column can be a data-backed column or formula column.

A filter expression for the measure filter pill consists of the filterable column, a filter operator, and one or more filter values. To configure the Filter properties, first select the measure pill in the Measure tray in the Insight panel. Then, in the Properties panel, for the Filter property, select the measure filter pill. In the Filter panel, configure the following properties to specify the filter expression.

Property	Control	Description	Comments
Column name	label	Displays the fully-qualified name of the selected column pill.	You can select the information icon to view more details about the column
Column label	text box	Displays the editable label for the column pill.
Date Part	drop down list	Select a Date Part value: Full, Year, Quarter, Month, or Day.	Date Part is only available for a measure filter pill of type Date or Timestamp/
Operator	drop down list	Select the filter operator.
Values	radial or check box	Select the filter value(s). Some filter operators do not require a filter value such as `Null` and `True`. The Between filter operator supports entering a range of values.	In addition to specific values, you can reference variables according to the measure filter context. For more information, see Valid referenceable variables in a measure filter. To specify a variable, enter `$` to see a list of available system and session variables.
Values	text box	Enter scalar values in the text box and use the `+` control to add the filter values. You can add more than one filter value, but only select one for the filter expression.	You cannot enter a variable using the text box. You must use a formula to specify a variable as a filter value in a filter expression for a measure filter.

Measure filter pill as a Formula

You use the Formula Builder to create a formula expression for a measure filter pill that is a formula. Depending on the filter operator, a formula expression can itself evaluate specific conditions and values. A filter value for a measure filter can be a scalar value or a referenceable variable. For more details, see Valid referenceable variables in a measure filter.

The following are the properties of a measure filter that uses a formula:

Property	Control	Description	Comments
Column label	text box	Displays the editable label for the formula pill.
Dynamic	toggle	Enable this option to select a filter expression session variable as a value
Formula	text box	Select to invoke the Formula Builder and create the filter expression	This property is visible only when you disable Dynamic
Operator	drop down list	Select the filter operator to use in the filter expression	This property is visible only when Dynamic is disabled.
Values	radial or check box	Select the filter value(s). Some filter operators do not require a filter value such as `Null` and `True`. With the Dynamic property enabled, search for and then select a date system variable or filter expression session variable.	In addition to specific values, you can reference variables according to the measure filter context. For more information, see Valid referenceable variables in a measure filter.

Valid referenceable variables in a measure filter

When creating the filter expression for a measure filter pill, whether using a column or a formula, you can reference different variables as filter values. There are multiple factors that govern which types of variables you can reference. These factors are as follows:

The type of the filter: column, filter expression formula, or dynamic formula
The context of the measure filter: insight, Incorta Analyzer Table, or Incorta Analyzer View
The filter expression you create for a formula measurefilter
The operator you select

Here are the types of variables that you can reference as a filter value (scalar or array) in a measure filter:

The following table shows the matrix of referenceable variables and the context of the measure filters.

Referenceable Variables	Context: Incorta Analyzer Table	Context: Incorta Analyzer View	Context: Insight	Type: Column (data-backed or formula)	Type: Formula (using filter expression)	Type: Dynamic Formula
date system varaible	✔	✔	✔	✔	✔	✔
external session variable	✔	✔	✔	✔	✔
filter expression session variable	✔	✔	✔			✔
global variable	✔	✔	✔		✔
internal session variable	✔	✔	✔	✔	✔
miscellaneous system variable	✔	✔	✔	✔	✔
presentation variable			✔	✔	✔

View access rights

In order to reference a column or variable in a measure filter, you must have View access rights to the grandparent object (physical schema or business schema) or the variable.