Table of Contents

Hierarchy and level properties

  • 2026-10-05
  • Jeroen ter Heerdt
Applies to:
  • ✔ Tabular Editor 2
  • Tabular Editor 3
    ✔ Desktop
    ✔ Business
    ✔ Enterprise
Note

This page was created with the help of AI.

This page covers the properties of user-defined hierarchies and of the levels in them. For properties that most objects share, see Common properties.

Hierarchy

A hierarchy is a named, ordered list of columns from one table that report authors drill down through in visuals and PivotTables, for example Year > Quarter > Month > Date or Category > Subcategory > Product. Each step is a level. Hierarchies don't change how the data is stored or calculated.

All the levels of a hierarchy must come from the table the hierarchy belongs to. To build a hierarchy across tables, first bring the columns into one table, for example with calculated columns that use RELATED.

In the TOM Explorer, you add levels to a hierarchy by dragging columns onto it. See Drag and drop objects.

Common properties

Hide Members

HideMembers · HierarchyHideMembersType · Options · compatibility level 1400+

Whether client tools hide members with a blank value. Use it for ragged hierarchies, where some branches have fewer levels than others. In a geography hierarchy Country > State > City, for example, some countries have no states, and with Default the user sees an empty member at the State level between the country and its cities. The Properties view shows HideMembers at compatibility level 1400 or higher.

Value Meaning
Default Shows all members, including blank ones. Use it for a balanced hierarchy.
HideBlankMembers Hides a member when its value is blank, and its child members appear directly under the parent.

Only blank members are hidden, and a placeholder value such as N/A still appears. Excel respects this property. Power BI ignores it and shows the blank members.

Level

A level is one step in a hierarchy. Each level points to a column of the hierarchy's table and has its own name, which can differ from the column's name. For example, a level named Month can point to a column named MonthName.

Common properties

Levels have no IsHidden or DisplayFolder of their own and follow their hierarchy in perspectives. If you translate the hierarchy's name, translate its level names too. The Best Practice Analyzer (BPA) rule Translate Hierarchy Level Names for All Cultures flags levels of visible hierarchies that have no translated name in one or more of the model's cultures.

Column

Column · Column · Basic

The column that provides the values of the level. It must be a column of the same table as the hierarchy, and a column can be used once in the same hierarchy. The level shows the column's values in the column's sort order. To show months in calendar order, set SortByColumn on the month name column (see @object-properties-columns).

A hidden column still works in a hierarchy. Hide the level columns to have users browse the data through the hierarchy.

Ordinal

Ordinal · int · Basic

The position of the level within the hierarchy, starting at 0 for the top level. The ordinals of the levels in a hierarchy must be 0, 1, 2 and so on, without gaps or duplicates.

Tabular Editor 3 keeps the ordinals numbered:

  • when you drag levels into a different order in the TOM Explorer, Tabular Editor renumbers all levels of the hierarchy
  • when you delete a level, Tabular Editor renumbers the remaining levels to close the gap
  • when you type a new Ordinal in the Properties view, Tabular Editor moves the level to that position and renumbers the other levels
  • when you add a level, it goes to the bottom of the hierarchy, unless you drop the column between two existing levels

You can set Ordinal for one level at a time. Tabular Editor 2 renumbers level ordinals in the same way when you drag levels into a different order and when you delete a level.

You can also drag levels onto another hierarchy in the same table to move them there. See Drag and drop objects.