Daily DAX : Day 342 INFO.HIERARACHIES

Power BI DAX: INFO.HIERARCHIES Function

Description

The INFO.HIERARCHIES function in DAX (Data Analysis Expressions) is used in Power BI to retrieve metadata about hierarchies defined in a tabular model. It returns a table containing information about the hierarchies, such as their names and associated attributes, within the current model context.

Syntax

INFO.HIERARCHIES()

Parameters: None. The function does not accept any parameters.

Return Value: A table with columns describing the hierarchies in the model, including:

• HIERARCHY_NAME: The name of the hierarchy.
• TABLE_NAME: The table containing the hierarchy.
• LEVELS: The number of levels in the hierarchy.
• PARENT_CHILD: Indicates if it is a parent-child hierarchy (True/False).

Use Case

The INFO.HIERARCHIES function is primarily used for:

• Model Documentation: To programmatically retrieve and document the structure of hierarchies in a Power BI model, useful for developers and analysts maintaining complex models.
• Dynamic Reporting: To create dynamic reports or visuals that adapt based on the hierarchy structure, such as displaying hierarchy metadata in a report.
• Debugging and Validation: To verify the existence and properties of hierarchies during model development or troubleshooting.

Example

Suppose you have a Power BI model with a hierarchy in a table called "Sales" that organizes data by Region -> Country -> City. You can use the following DAX query to retrieve hierarchy details:

EVALUATE INFO.HIERARCHIES()

Output (Sample):

TABLE_NAME	HIERARCHY_NAME	LEVELS	PARENT_CHILD
Sales	Geography	3	FALSE

Notes

• This function is part of the DAX metadata functions introduced in later versions of Power BI and SQL Server Analysis Services (SSAS).
• It is typically used in DAX queries (e.g., via DAX Studio) rather than in measures or calculated columns.
• Requires a tabular model with defined hierarchies to return meaningful results.