Daily DAX : Day 340 INFO.LEVELS

Power BI DAX: INFO.LEVELS Function

Description

The INFO.LEVELS function in Power BI DAX (Data Analysis Expressions) is used to retrieve metadata about the levels of a hierarchy in a tabular model. It returns a table containing information about the levels within a specified hierarchy, such as their names, order, and other properties.

Syntax

INFO.LEVELS(Hierarchy)
    

Parameters:

• Hierarchy: The name of the hierarchy (e.g., [TableName].[HierarchyName]) or a reference to a hierarchy in the model.

Return Value

A table with the following columns:

• HIERARCHY_NAME: Name of the hierarchy.
• LEVEL_NUMBER: The level number in the hierarchy (starting from 0 for the top level).
• LEVEL_NAME: Name of the level.
• LEVEL_CAPTION: Display name of the level.
• LEVEL_UNIQUE_NAME: Unique identifier for the level.

Use Case

The INFO.LEVELS function is primarily used for metadata analysis in advanced Power BI or Analysis Services scenarios. It is helpful when you need to dynamically explore or document the structure of hierarchies in a data model, such as in:

• Dynamic Reporting: To create reports or visuals that display hierarchy metadata.
• Model Documentation: To extract and document the structure of hierarchies for auditing or governance.
• Custom Calculations: To dynamically reference hierarchy levels in complex DAX calculations.

Example

Suppose you have a hierarchy Geography in a table Locations with levels: Country, State, and City. You can use INFO.LEVELS to retrieve its structure.

EVALUATE
INFO.LEVELS('Locations'[Geography])

    

Output:

HIERARCHY_NAME	LEVEL_NUMBER	LEVEL_NAME	LEVEL_CAPTION	LEVEL_UNIQUE_NAME
Geography	0	Country	Country	[Locations].[Geography].[Country]
Geography	1	State	State	[Locations].[Geography].[State]
Geography	2	City	City	[Locations].[Geography].[City]

Notes

• This function is part of the INFO family of DAX functions, which are designed for metadata retrieval.
• It is mainly used in DAX queries (e.g., in DAX Studio) rather than in calculated columns or measures.
• Requires a valid hierarchy in the data model to work.