Filters and Filter Sets

Filters

Filters are used at a dashboard level to slice the data displayed in the dashboard widgets (eg. basic chart, table, matrix).

Filter Sets

A filter set is a group of filters. Within dashboard widgets, filters can be applied by specifying the name and filter set. For example, we can have two charts tracking yearly financials, with one displaying an Account from the first filter set, eg. ARR, and the other chart displaying an Account from the second, eg. Revenue.

Basic Dimension Filter

The most commonly used filters are basic dimension type, which can be selected from dimensions available in the tenant. Certain addtional settings can be specified at this level, such as setting a custom label, specifying All Members or Leaf Members, and whether the display should be Name, Caption, or Key.

Custom Filters

Click the edit button for a filter set, then click "Add Custom".

Date

Custom date filters are defined by a date range.

List

Custom list filters are defined by either a dimension query or a SQL query. Members returned by the query are displayed as a flat list.

Tree

Custom tree filters are defined by either a dimension query or a SQL query. Members returned by the query are displayed in a tree that replicates the hierarchy structure.

There are several ways in which to display a hierarchy, considering the query might only return parts of a hierarchy. Let's assume a query with the following spec:

• [Period].[Q1 2026].SelfAndDescendants
• [Period].[Aug 2026].Self
• [Period].[Sep 2026].Self

The query returns the following members: [Q1 2026], [Jan 2026], [Feb 2026], [Mar 2026], [Aug 2026], [Sep 2026]

Orphaned Tree

An orphaned view displays strictly the values from the query. When the parent of a member is not included in the query results, the member is displayed at the root of the tree. For the example above, an orphaned tree would look like this:

• Q1 2026
  • Jan 2026
  • Feb 2026
  • Mar 2026
• Aug 2026
• Sep 2026

note

To use an orphaned view, make sure the Root Member is not set for the query.

Full Tree View

A full tree view takes a common root member (this is a root for the tree, does not have to be the dimension root) and displays all descendants of the root that sit between it and the query results. These added members can be expanded but are not selectable. In addition we can choose whether to include the root member in the tree or not. If the root member is not set, the tree view will be orphaned (see above).

note

To use a full tree view, make sure the Root Member is set.

If the Include Root flag is not set, the displayed tree will not display the root, unless that member is part of the query results. Assuming a root of [FY 2026], the full tree view would look like below:

• Q1 2026
  • Jan 2026
  • Feb 2026
  • Mar 2026
• Q2 2026
  • Aug 2026
  • Sep 2026

Q2 2026 will be displayed, but not selectable. Other descendants of FY 2026 that are not required to navigate to the query results will not be displayed at all (Q3 2026, Oct 2026 etc). If the Include Root flag is set, the resulting tree will also contain the selected root member:

• FY 2026
  • Q1 2026
    • Jan 2026
    • Feb 2026
    • Mar 2026
  • Q2 2026
    • Aug 2026
    • Sep 2026

Finally, if we want to use a parameter or global variable for the root member, the Custom flag must be set, which will enable the end-user to type directly in the Root Member field things like {Parameter.Period.0.Key}

important

If a full tree view is used and the query returns members outside of the descendants of the root member, outside members will be displyed orphaned.

Switching Hierarchies with a Filter

A dimension query runs against one hierarchy of its dimension. The Hierarchy field sets it:

• In the Dimension Query Editor it sits between Dimension and Security profile.
• In the Model Query Editor it sits left of Properties, for the selected dimension.

Pick a hierarchy from the drop-down (*default* is the dimension's primary hierarchy), or type its name. The field is hidden when dimension roles are used, since a role always uses the primary hierarchy.

When the hierarchy changes, the editor asks whether to remove the existing member spec, because members picked in one hierarchy may not exist in another. In the Model Query Editor, a hierarchy change on a row or column dimension applies to every range of that axis.

Letting the user choose the hierarchy

The Hierarchy field also accepts a variable, so a dashboard filter can choose the hierarchy at run time:

1. Add a custom List filter (for example Hierarchy) defined by a SQL query that returns one row per hierarchy. The primary hierarchy has an empty name, so return * for it, which Una reads as the primary hierarchy:

   select row_number() over (order by [Hierarchy]) as [Key],
          iif([Hierarchy] = '', '*', [Hierarchy]) as [Name],
          iif([Hierarchy] = '', '*default*', [Hierarchy]) as [Caption]
   from [App_DimensionHierarchies]
   where [Dimension] = 'Period'

2. In the query editor, drag the filter's chip from the Variables section onto the Hierarchy field, or type the token. The field takes the filter's Name, for example {Parameter.Hierarchy.0.Name}, because a hierarchy name is text and Key must be a number.

While the hierarchy comes from a variable, the editor cannot know which members exist, so it does not list available members: add member specs with the + button. The input grid's member pickers show the current member read-only for the same reason. A dependent filter whose dimension query uses the same token re-resolves its members whenever the Hierarchy filter changes.

note

App_DimensionHierarchies is an application table. Users who are not administrators need a relational read grant on it, or the dashboard must have Run queries as owner turned on, so the filter runs with its owner's permissions.