Daily DAX : Day 136 INFO.ROLES

What is INFO.ROLES?

INFO.ROLES is a DAX function in Power BI that exposes the metadata about security roles defined in a semantic model, effectively replacing the older TMSCHEMA_ROLES DMV query syntax. It was part of the wave of over 50 INFO.* functions introduced in the December 2023 Power BI Desktop update, designed to make model metadata more accessible directly through DAX. These functions mirror the tabular model’s schema rowsets, providing a native DAX way to inspect objects like tables, measures, columns, and—in this case—roles.

The function returns a table with details about the roles in your Power BI model, such as their names, descriptions, and potentially other attributes like the DAX filter expressions tied to them (though the exact columns can vary slightly based on the model and Power BI version).

How Does It Work?

You use INFO.ROLES in a DAX query, typically within the DAX Query View in Power BI Desktop or tools like DAX Studio. It’s not meant for use in measures, calculated columns, or calculated tables (those will throw errors); it’s strictly for querying metadata. Here’s a basic example of how you’d call it:

DAX

EVALUATE

INFO.ROLES()

Running this query returns a table with columns like:

    RoleID: A unique identifier for the role.

    Name: The name of the role (e.g., "West Manager").

    Description: Any description added to the role (optional).

    ModelPermission: Indicates the permission level (typically “Read” for most roles in Power BI).

    Other potential fields: Depending on updates, it might include the DAX filter expression or creation/modification timestamps, though these details are less consistently documented.

Use Case

The primary use case for INFO.ROLES is model documentation and auditing. Let’s say you’ve built a Power BI report with Row-Level Security (RLS) roles to restrict data access—like one role for “Sales Team” and another for “HR Team.” Over time, as the model grows or team members change, you need to verify what roles exist, what they’re called, and what they’re filtering. INFO.ROLES lets you pull this info directly without digging into the model’s RLS settings manually or relying on external tools like SQL Server Management Studio (SSMS) to query the older DMVs.

Practical Example

Imagine a retail company with a Power BI model:

    Roles: “Store Managers” (filters to specific stores) and “Regional Directors” (filters to regions).

    You run:

    DAX

    EVALUATE

    INFO.ROLES()

    The result might show:

    RoleID

   

    Name

   

    Description

   

    ModelPermission

    1

   

    Store Managers

   

    Access to store data

   

    Read

    2

   

    Regional Directors

   

    Access to region data

   

    Read

You could then copy this output (e.g., into Excel) or join it with other INFO.* functions (like INFO.TABLES) to build a full documentation report of your model’s structure and security.

Combining with Other Functions

To get more context—like which tables the roles filter—you might combine it with INFO.ROLETABLEPERMISSIONS() (another DMV-based function), which details the table-level permissions for each role. For example:

DAX

EVALUATE

NATURALLEFTOUTERJOIN(

    INFO.ROLES(),

    SELECTCOLUMNS(

        INFO.ROLETABLEPERMISSIONS(),

        "RoleID", [RoleID],

        "TableName", [TableName],

        "FilterExpression", [FilterExpression]

    )

)

This could show that “Store Managers” filters Sales[StoreID] = 5, giving you a complete picture of the RLS setup.

Why It’s Useful

    Simplifies Metadata Access: Before these INFO.* functions, you’d need to use SSMS or DAX Studio with DMV syntax (e.g., SELECT * FROM $SYSTEM.TMSCHEMA_ROLES), which was less integrated into Power BI’s workflow.

    Documentation: Exporting role details helps maintain transparency and compliance, especially in regulated industries.

    Troubleshooting: Quickly check if roles are misconfigured or missing without opening the “Manage Roles” dialog.

Limitations

    Query-Only: You can’t use INFO.ROLES in a measure or calculated column—it’s for queries only.

    Dynamic Models: If you’re working locally in Power BI Desktop, the model’s database ID changes each session, so refreshing metadata queries requires updating connection details.

    Evolving Features: As of February 25, 2025, documentation is still catching up, and some columns returned by INFO.ROLES might not be fully detailed in official sources yet.

Wrap-Up

INFO.ROLES is a handy tool for inspecting and documenting the security roles in your Power BI model, rooted in the DMV framework but delivered through a modern DAX interface. It’s perfect for auditing RLS setups or generating reports about your model’s security structure. If you’re working on something specific with it—like validating a role’s filters—let me know, and I can tailor the explanation further! Did this hit the mark for what you were asking?