Document Power BI Roles – RLS, OLS & Members

This blog post explains how to extract the Power BI semantic model roles information using DAX Query view.

Since its release, DAX Query View has become a go-to tool for many of us. Being able to run scripts directly within Power BI Desktop or the Service makes it so much easier to debug and format code on the fly.

Beyond just testing measures, I’ve found it incredibly helpful for managing model metadata—specifically when it comes to Roles. Whether you are working on Row Level Security (RLS) or Object Level Security (OLS), knowing exactly what is defined in your model is essential.

I have multiple DAX expressions which can be very useful for you to improve the productivity.

1. DAX Query View Scripts – Model Issues, Dependency, Model summary etc.
2. DAX Vertipaq Analyzer Scripts

Power BI semantic model may have multiple roles to handle business requirements. It is important to understand the roles defined on the models for various reasons and added some of them below.

Better Documentation: Every technical hand-over should include a clear list of roles and their specific definitions.

Security Migration: If you’re moving governance to a platform like Databricks Unity Catalog, you need a clean inventory of your existing permissions to recreate them accurately.

We typically follow below 3 steps.

1. Create a role with proper name and definition – simple filter expression at a column and table level or complex dynamic DAX expression with filter context
2. Create role can be still used to apply Object Level Security using tools like Tabular Editor or TMDL view with script
3. Assign the roles in Power BI service.

Example – I have below roles defined in Power BI semantic model

I have 2 different roles and used them for both Row Level Security (RLS) & Object Level Security (OLS).

All the DAX expressions are available in my GitHub Code Repo here

Below DAX expressions is useful to extract the roles and the usage of them. The first one is to help understand the list of roles and properties of the role and anything specific at table level for both RLS and OLS

//Author - Hariharan Rajendran
//Contact - https://www.linkedin.com/in/imhariharanr/
//version - 1.2

//The below expression is to help identify the roles defined in Power BI Semantic model and understand all the filter expression applied on the tables
EVALUATE
	//select only required columns from Tables. Extract only Table Name
	VAR _Table = SELECTCOLUMNS(
		INFO.TABLES(),
		"TableID", [ID],
		"TableName", [Name]
	)
	//Get the required cokumns from Role Tabel permission
	VAR _TableExp = SELECTCOLUMNS(
		INFO.TABLEPERMISSIONS(),
		"RoleID", [RoleID],
		"TableID", [TableID],
		"RoleExp", [FilterExpression],
		"TypeofPermission", SWITCH(
			[MetadataPermission],
			0, "Default - No restriction",
			1, "None - Object is hidden as part of OLS",
			"Read - Object is available for read"
		)
	)
	//Get the required columns from roles
	VAR _Role = SELECTCOLUMNS(
		INFO.ROLES(),
		"RoleID", [ID],
		"RoleName", [Name]
	)
	RETURN
		NATURALLEFTOUTERJOIN(
			NATURALLEFTOUTERJOIN(
				_TableExp,
				_Table
			),
			_Role
		)

Below expression is useful to get the column level permission defined specifically for the OLS.

//Author - Hariharan Rajendran
//Contact - https://www.linkedin.com/in/imhariharanr/
//version - 1.2

//The below expression is to help identify the roles defined in Power BI Semantic model and understand all the filter expression applied on the tables specifically for OLS
EVALUATE
	VAR _TableExp = SELECTCOLUMNS(
		INFO.TABLEPERMISSIONS(),
		"TablePermissionID", [ID],
		"RoleID", [RoleID],
		"TableID", [TableID],
		"RoleExp", [FilterExpression]
	)
	VAR _Role = SELECTCOLUMNS(
		INFO.ROLES(),
		"RoleID", [ID],
		"RoleName", [Name]
	)
	VAR _Table = SELECTCOLUMNS(
		INFO.TABLES(),
		"TableID", [ID],
		"TableName", [Name]
	)
	VAR _ColumnPer = SELECTCOLUMNS(
		INFO.COLUMNPERMISSIONS(),
		[TablePermissionID],
		[ColumnID],
		"TypeofPermission", SWITCH(
			[MetadataPermission],
			0, "Default - No restriction",
			1, "None - Object is hidden as part of OLS",
			"Read - Object is available for read"
		)
	)
	VAR _columns = SELECTCOLUMNS(
		INFO.COLUMNS(),
		"ColumnID", [ID],
		"ColumnName", [ExplicitName]
	)

	RETURN
		SELECTCOLUMNS(
			NATURALLEFTOUTERJOIN(
				NATURALLEFTOUTERJOIN(
					NATURALLEFTOUTERJOIN(
						NATURALLEFTOUTERJOIN(
							_ColumnPer,
							_TableExp
						),
						_columns
					),
					_Table
				),
				_Role
			),
			[RoleName],
			[TableName],
			[ColumnName],
			[TablePermissionID],
			[TypeofPermission]
		)

Below expression is helpful to understand the members assigned to the role in Power BI service.

//Author - Hariharan Rajendran
//Contact - https://www.linkedin.com/in/imhariharanr/
//version - 1.2

//The below expression is to help identify the roles assigned to different users and groups
EVALUATE
	VAR _RoleMembers = SELECTCOLUMNS(
		INFO.ROLEMEMBERSHIPS(),
		[RoleID],
		[MemberName],
		[IdentityProvider],
		"MemberType", SWITCH(
			[MemberType],
			2, "User",
			3, "AD group",
			"Auto"
		)
	)
	VAR _Role = SELECTCOLUMNS(
		INFO.ROLES(),
		"RoleID", [ID],
		"RoleName", [Name]
	)
	RETURN
		SELECTCOLUMNS(
			NATURALLEFTOUTERJOIN(
				_RoleMembers,
				_Role
			),
			[RoleName],
			[MemberType],
			[IdentityProvider],
			[MemberName]
		)

Hope the above scripts help you to get the complete information for the roles in Power BI semantic models. Please share your feedback, Happy Learning!!