Daily DAX : Day 302 INFO.RELATIONSHIPS

Power BI DAX INFO.RELATIONSHIPS Function

Description

The INFO.RELATIONSHIPS function in Power BI DAX returns a table describing the relationships in the current data model. It provides metadata about the relationships, including the tables and columns involved, the relationship's direction, and its active status.

Syntax

INFO.RELATIONSHIPS()

Parameters: None. This function takes no arguments.

Return Value

A table with the following columns:

• FromTable: Name of the table on the "many" side of the relationship.
• FromColumn: Name of the column in the "many" side table.
• ToTable: Name of the table on the "one" side of the relationship.
• ToColumn: Name of the column in the "one" side table.
• IsActive: Boolean indicating if the relationship is active (TRUE) or inactive (FALSE).
• RelationshipType: Type of relationship (e.g., "OneToMany").

Use Case

The INFO.RELATIONSHIPS function is primarily used for model introspection and debugging. It helps users understand the structure of relationships in their data model, which is useful for:

• Documentation: Automatically generating a report of all relationships in the model for documentation purposes.
• Troubleshooting: Identifying issues with relationships, such as inactive relationships or incorrect column mappings.
• Dynamic Analysis: Building dynamic DAX calculations that depend on the relationship structure, such as validating relationships before performing calculations.

Example

Suppose you have a data model with a Sales table and a Products table, related by a ProductID column. You can use INFO.RELATIONSHIPS to inspect the relationship:

EVALUATE
INFO.RELATIONSHIPS()
    

Output (example):

FromTable	FromColumn	ToTable	ToColumn	IsActive	RelationshipType
Sales	ProductID	Products	ProductID	TRUE	OneToMany

Notes

• This function is part of the INFO family of DAX functions, introduced to provide metadata about the data model.
• It is typically used in DAX queries (e.g., in DAX Studio) rather than in calculated columns or measures.
• It does not modify the data model or relationships; it only retrieves information.

Limitations

• Requires a well-defined data model with relationships to return meaningful results.
• Only provides information about direct relationships, not inferred or indirect relationships.