Marco Russo, BI expert from sqlbi.com, shows you how to handle hierarchies in DAX in this 55-minute, demo-focused video. Although DAX does not have a specific syntax for working with categories, hierarchies can be defined in the data model in Power Pivot and in SQL Server Analysis Services Tabular. For those reasons, it is important to learn how to write hierarchy-based calculations in DAX. This video discusses both the data models and the needed calculations when using hierarchies.

You begin creating a hierarchy in a data model by choosing a column that you wish to show as the levels of your hierarchy. It is a good habit to hide the columns used in hierarchy levels from the client tools, so that the same information would not be displayed in two different places, which could confuse users. Any column of a table can be used as a hierarchy level, but each hierarchy can only use columns of the same table. If you want to create a hierarchy using columns from different tables, you should copy all the columns into a single table, for example using the RELATED function.

Once you have a hierarchy, you might want to use different calculations at different levels of the same hierarchy. To do that, you need to write a DAX formula that identifies the level in which the calculation is performed, and, then, to apply the proper calculation based on this information. A key DAX function, ISFILTERED, allows you to detect whether a filter has been applied to a particular column.

When the data source has a parent-child hierarchy, you have to naturalise that hierarchy as a regular one in DAX. Starting from a simple data model in Excel which contains parent-child hierarchies, Marco shows how to create the hierarchy step-by-step in your data model, applying the proper DAX calculations to the measures, in order to hide hierarchy members that would not be useful. PATH is an important DAX function that returns a string consisting of all the keys of the nodes that were included in the path, starting from the root level of the hierarchy, all the way to the node in question. With this information, you can obtain the content of any level of a hierarchy by using yet another function, PATHITEM, that could be used as an argument to LOOKUPVALUE, which transforms a key into a description. When a row of a table corresponds to a node that is not at the lowest level of the hierarchy, you have to propagate such a name to the lower levels. You can just copy the same node name to the lower levels. At the same time, use PATHLENGTH to detect when the hierarchy has a shorter path than the number of available levels.

Once you have created all the columns required by the levels of a hierarchy, you should create the hierarchy in the Diagram View of the data model.

Finally, a variation of this calculation can be used if you would like to display a value directly related to an intermediate node as fictitious children of the node itself.

Hierarchies can be easily created in a data model to provide metadata to client tools to simplify navigation. However, DAX does not have a direct knowledge of the hierarchies, and you have to implement calculation logic in an explicit way, as you this video explains.

Log in or purchase access below to the premium version of this content.