Daily DAX : Day 157 INFO.PERSPECTIVEHIERARCHIES

 The INFO.PERSPECTIVEHIERARCHIES function in Power BI DAX is one of the newer "INFO" functions introduced to provide metadata about a semantic model. Specifically, this function retrieves information about the hierarchies defined within perspectives in your Power BI model. Perspectives are a feature in tabular models that allow you to define a subset of tables, columns, measures, and hierarchies tailored for specific user groups or reporting needs, simplifying the model for targeted analysis.

What It Does

The INFO.PERSPECTIVEHIERARCHIES function returns a table containing details about the hierarchies associated with perspectives. Each row in the resulting table represents a hierarchy within a perspective, and the columns typically include metadata such as:

    PerspectiveID: A unique identifier for the perspective.

    HierarchyID: A unique identifier for the hierarchy.

    Name: The name of the hierarchy.

    PerspectiveName: The name of the perspective containing the hierarchy.

    TableID: The identifier of the table to which the hierarchy belongs.

This function is part of the broader family of INFO functions (introduced in late 2023), which expose metadata from the model in a way that can be queried directly using DAX. These functions are particularly useful for documenting models, auditing configurations, or creating dynamic reports based on the model's structure.

Syntax

The function has no parameters and is used within a DAX query, typically with the EVALUATE statement:

EVALUATE

INFO.PERSPECTIVEHIERARCHIES()

When executed, it returns the table of hierarchy metadata for all perspectives in the model.

Use Case

The INFO.PERSPECTIVEHIERARCHIES function is primarily a tool for model introspection and documentation rather than direct data analysis. Here’s a practical use case:

Scenario: Documenting Hierarchies in a Multi-Perspective Model

Imagine you’re working on a complex Power BI model designed for a large organization. The model includes multiple perspectives:

    A "Sales" perspective with hierarchies like "Year > Quarter > Month" and "Region > Country > City."

    A "Finance" perspective with hierarchies like "Account > Category > Subcategory."

    An "HR" perspective with hierarchies like "Department > Team > Employee."

As a report developer or administrator, you need to provide documentation to end-users or other developers about which hierarchies are available in each perspective, especially since not all hierarchies are visible in every perspective. Manually tracking this across a large model is time-consuming and error-prone.

Solution

You can use INFO.PERSPECTIVEHIERARCHIES in DAX Query View in Power BI Desktop to extract this information dynamically:

EVALUATE

INFO.PERSPECTIVEHIERARCHIES()

This query returns a table listing all hierarchies across all perspectives. To make it more useful, you could join it with other INFO functions (like INFO.TABLES or INFO.PERSPECTIVES) to enrich the output with table names or additional context. For example:

EVALUATE

VAR Hierarchies = INFO.PERSPECTIVEHIERARCHIES()

VAR Tables = INFO.TABLES()

RETURN

SELECTCOLUMNS(

    NATURALLEFTOUTERJOIN(Hierarchies, Tables),

    "PerspectiveName", [PerspectiveName],

    "HierarchyName", [Name],

    "TableName", [TableName]

)

This enhanced query provides a readable output with the perspective name, hierarchy name, and the associated table name, making it easier to understand the structure.

Practical Application

    Model Documentation: Export the results to Excel or a Power BI report page to create a reference guide for users, showing which hierarchies they can use in each perspective.

    Auditing: Verify that hierarchies are correctly assigned to perspectives, ensuring no critical ones are missing or misplaced.

    Dynamic Reporting: Build a report that adapts based on the model’s metadata, such as a dropdown of available hierarchies per perspective for users to explore.

Key Benefits

    Automation: Eliminates the need to manually inspect the model or use external tools like DAX Studio to retrieve hierarchy metadata.

    Integration: Since it’s a native DAX function, you can combine it with other DAX functions to filter, join, or format the output as needed.

    User Support: Helps bridge the gap between model developers and end-users by providing transparency into the structure of perspectives and their hierarchies.

Limitations

    The function only works if perspectives and hierarchies are defined in the model. If your model doesn’t use perspectives, the output will be empty or irrelevant.

    It’s metadata-focused, so it’s not useful for analyzing actual data (e.g., sales figures or quantities).

    Requires DAX Query View or a tool that supports DAX queries, as it’s not designed for direct use in measures or calculated columns.

Conclusion

The INFO.PERSPECTIVEHIERARCHIES function is a niche but valuable tool for Power BI developers working with complex models that leverage perspectives. Its primary use case is in model governance and documentation, making it easier to manage and communicate the structure of hierarchies tailored to different user needs. By integrating it into your workflow, you can streamline administrative tasks and enhance the usability of your Power BI solutions.