Edit

INFO.TABLEPERMISSIONS

Applies to: Calculated column Calculated table Measure Visual calculation DAX query

Returns a table with information about all table storage in the semantic model. This information helps you understand the model.

Syntax

INFO.TABLEPERMISSIONS([<Restriction name>, <Restriction value>], ...)

Parameters

Parameters are optional for this DAX function. When parameters are used, both must be given. More than one pair of parameters is allowed. The restriction name and value are text and entered in double-quotes.

Term Definition
Restriction name Name of the restriction used to filter the results.
Restriction value Value used to filter the results of the restriction.

Restrictions

Typically, all columns of the DAX function results can be used as a restriction. Additional restrictions may also be allowed.

Return value

A table with the following columns:

Column Description
[ID] A reference to the object. IDs are usually autogenerated and should not be changed after the model is created. Data type is unsigned long (4 bytes).
[RoleID] The ID of the role that provides this permission.
[TableID] The ID of the table on which this permission applies.
[FilterExpression] The filter expression in the permission.
[ModifiedTime] When the permission was last modified.
[State] The numerical repesentation of the state of the permission.
[ErrorMessage] Any error message with the permission.
[MetadataPermission] A numerical representation of the permission provided. 0 means no access control is enforced, while 1 means access is restricted and 2 means access is unrestricted

Remarks

Can only be run by users with write permission on the semantic model and not when live connected to the semantic model in Power BI Desktop. This function can be used in DAX queries, and can't be used in calculations.

Example 1 - DAX query

The following DAX query can be run in DAX query view:

EVALUATE
	INFO.TABLEPERMISSIONS()

This DAX query returns a table with all of the columns of this DAX function.

Example 2 - DAX query with joins

The following DAX query can be run in DAX query view:

EVALUATE
	VAR _INFO =	INFO.TABLEPERMISSIONS()

    VAR MetadataPermissionEnum =
	DATATABLE(
	"MetadataPermission",INTEGER,
	"MetadataDescription",STRING,
	{
	{0,"Default behavior - no access control is enforced"},
	{1,"Access to the data is restricted"},
	{2,"Access to the data is unrestricted"}
	}
	)
    VAR StateEnum =
    DATATABLE(
    	"State",INTEGER,
    	"StateName",STRING,
    	{
        	{1,"Ready"},
        	{3,"NoData"},
        	{4,"CalculationNeeded"},
        	{5,"SemanticError"},
        	{6,"EvaluationError"},
        	{7,"DependencyError"},
        	{8,"Incomplete"},
        	{10,"ForceCalculationNeeded"}
    	}
    	)
	VAR _CombinedTable =
	NATURALLEFTOUTERJOIN(
		_INFO,
		MetadataPermissionEnum
	)

    VAR _CombinedTable2 =
	NATURALLEFTOUTERJOIN(
		_CombinedTable,
		StateEnum
	)

    VAR _ModelRoles = 
		SELECTCOLUMNS(
			INFO.ROLES(),
			"RoleID", [ID],
			"Role Name", [Name]
		)
	VAR _ModelTables = 
		SELECTCOLUMNS(
			INFO.TABLES(),
			"TableID", [ID],
			"Table Name", [Name]
		)

	VAR _CombinedTable3 =
	NATURALLEFTOUTERJOIN(
		_CombinedTable2,
		_ModelRoles
	)
	VAR _CombinedTable4 =
	NATURALLEFTOUTERJOIN(
		_CombinedTable3,
		_ModelTables
	)
	
	RETURN
	
		SELECTCOLUMNS(
			_CombinedTable4,
			"Table Permission ID", [ID],
			"Filter Expression", [FilterExpression],
			"Modified Time", [ModifiedTime],
            "State Name", [StateName],
			"Error Message", [ErrorMessage],
			"Metadata permission description", [MetadataDescription],
			"Role Name", [Role Name],
			"Table Name", [Table Name]
			)
			
	ORDER BY [Table Permission ID]

This DAX query returns a table with only the specified columns and joining to other INFO DAX functions and the table permissions table.

Enumerations

Columns returned in this INFO function give the ID of an enumeration, or lookup table. Here are the values for each enumeration in this DAX function.

ObjectState

Value Name Description
1 Ready Object is refreshed, contains up-to-date data, and is queryable.
3 NoData Object is queryable but contains no data. Refresh it to bring in data. Applies to non-calculated objects, such as DataColumns, partitions, and Tables.
4 CalculationNeeded Object is not queryable and contains no data. It needs to be refreshed to become functional. Applies only to calculated objects, such as calculated columns, hierarchies, and calculated tables.
5 SemanticError Object is in an error state because of an invalid expression. It is not queryable.
6 EvaluationError Object is in an error state because an error occurred during expression evaluation. It is not queryable.
7 DependencyError Object is in an error state because some of its calculation dependencies are in an error state. It is not queryable.
8 Incomplete Some parts of the object have no data. Refresh the object to add the rest of the data. The object is queryable. Applies to non-calculated objects, such as DataColumns, partitions, and tables.
10 ForceCalculationNeeded The data is possibly outdated, but is in a queryable state. Applies only for CalculatedTable.

This table is based on the official documentation.

To join with INFO functions use this DAX query.

EVALUATE
	DATATABLE(
	"State",INTEGER,
	"StateName",STRING,
	{
    	{1,"Ready"},
    	{3,"NoData"},
    	{4,"CalculationNeeded"},
    	{5,"SemanticError"},
    	{6,"EvaluationError"},
    	{7,"DependencyError"},
    	{8,"Incomplete"},
    	{10,"ForceCalculationNeeded"}
	}
	)

See also

  • Last updated on