Edit data models in the Power BI service (preview)
Power BI allows users to modify existing data models in the Power BI service using actions such as editing relationships, creating DAX measures and managing RLS. In this experience, users can work and collaborate simultaneously on the same data model.
Enable the preview feature
Editing data models in the Power BI service is automatically enabled for semantic models stored in My Workspace. To open the data model for semantic models stored in collaborative workspaces, you must turn on the preview feature for that workspace by completing the following steps:
- In the Power BI service, select Settings for the workspace where you want to enable the preview feature.
- Select Advanced > Data model settings > Users can edit data models in the Power BI service (preview)
- Select Save to see the new experience for semantic models in your workspace.
Note
Enabling the edit data models in the Power BI service preview doesn't apply to editing a semantic model through an API or an XMLA endpoint.
Open the data model
You can open the data model for your semantic model in the following ways:
- From the workspace content list, select More options (...) for the semantic model and select Open data model.
- From the data hub content list, select More options (...) for the semantic model and select Open data model.
- From the semantic model details page, select Open data model.
- From edit mode for a report connected to the semantic model, select Open data model to open the corresponding data model in another tab.
Model data
When you open your data model you can see all the tables, columns, and relationships in your model. You can now edit your data model, and any changes are automatically saved.
Create measures
To create a measure (a measure is a collection of standardized metrics) select the table in the Data Pane and select the New measure button from the ribbon, as shown in the following image.
Enter the measure into the formula bar and specify the table and the column to which it applies. Similar to Power BI Desktop, the DAX editing experience in the Power BI service presents a rich editor complete with autocomplete for formulas (intellisense).
You can expand the table to find the measure in the table.
Create calculated columns
To create a calculated column select the table in the Data Pane and select the New column button in the ribbon, as shown in the following image.
Enter the calculated column into the formula bar and specify the table to which it applies. Similar to Power BI Desktop, the DAX editing experience in the Power BI service presents a rich editor complete with autocomplete for formulas (intellisense).
You can expand the table to find the calculated column in the table.
Create calculated tables
To create a calculated table select the table in the Data Pane and select the New table button in the ribbon, as shown in the following image.
Enter the calculated table into the formula bar. Similar to Power BI Desktop, the DAX editing experience in the Power BI service presents a rich editor complete with autocomplete for formulas (intellisense). You can now see the newly created calculated table in your model.
Create a relationship
There are two ways to create a new relationship in the Power BI Service.
The first method is to drag the column from one table in the relationship diagram to the column of the other table to create the relationship.
The other method of creating a relationship is by selecting Manage relationships in the ribbon as shown in the following image.
This will open the revamped Manage relationships dialog. From here, you can select New relationship to create a new relationship in your model.
From here, configure the relationship properties, and select the Ok button when your relationship is complete to save the relationship information.
Edit a relationship
There are three ways to edit an existing relationship in the Power BI Service.
The first method to edit a relationship is using the Editing relationships in the Properties pane, where you can select any line between two tables to see the relationship options in the Properties pane. Be sure to expand the Properties pane to see the relationship options.
The next method is to right-click an existing relationship in the diagram view and select Properties.
In the window that appears, configure the relationship properties, and select the Ok button when your relationship is complete to save the relationship information.
The third method is by selecting Manage relationships in the ribbon. In the Manage relationships dialog you can choose a relationship to edit and then select Edit.
Alternatively, you can select Edit from the context menu of a given relationship in the dialog.
From here, configure the relationship properties, and select the Ok button when editing your relationship is complete to save the relationship information.
See a list of all your relationships
Selecting Manage relationships in the ribbon opens the revamped Manage relationships dialog which provides a comprehensive view of all your relationships, along with their key properties, in one convenient location. From here you can then choose to create new relationships or edit an existing relationship.
Additionally, you have the option to filter and focus on specific relationships in your model based on cardinality and cross filter direction.
Set properties
You can change the properties for a given object using the Properties pane. You can set common properties across multiple objects at once by holding down the Ctrl key and selecting multiple objects either in the relationship diagram or Data pane. When multiple objects are highlighted, changes applied in the Properties pane apply to all selected objects.
For example, you could change the data type for multiple columns by holding down the Ctrl key, selecting columns, then changing the data type setting in the Properties pane.
Set your own date table
To set a date table, select the table you want to use as a date table in the Data pane, then right-click the table and choose Mark as date table > Mark as date table in the menu that appears as shown in the following image.
Next, specify the date column by selecting it from the dropdown menu within the Mark as date table dialog.
Setting your own date table follows the same behavior as what exists in Power BI Desktop. Further details on column validation, scenarios for creating your own date table, and impact on date hierarchies can be found in the date tables documentation
Define row-level security roles and rules
You can define security roles by taking the following steps:
From the ribbon, select Manage roles.
From the Manage roles window, select New to create a new role.
Under Roles, provide a name for the role and select enter.
Under Select tables, select the table to which you want to apply a row-level security filter.
Under Filter data, use the default editor to define your roles. The expressions created return a true or false value.
Note
Not all row-level security filters supported in Power BI can be defined using the default editor. Limitations include expressions that today can only be defined using DAX, including dynamic rules such as username or userprincipalname. To define roles using these filters, switch to use the DAX editor.
Optionally select Switch to DAX editor to use the DAX editor to define your role. You can switch back to the default editor by selecting Switch to default editor. All changes made in either editor interface persist when switching interfaces when possible.
When defining a role using the DAX editor that can't be defined in the default editor, if you attempt to switch to the default editor you'll be prompted with a warning that switching editors might result in some information being lost. To keep this information, select Cancel and continue only editing this role in the DAX editor.
Select Save to save the role.
Once the role is saved, select Assign to add users to the role. Once assigned, select Save to save the role assignments and close the RLS settings modal.
Create layouts
You can create layouts of your model that contain only a subset of the tables in your model. This reorganization can help provide a clearer view into the tables you want to work with, and make working with complex semantic models easier. To create a new layout with only a subset of the tables, select the + button next to the All tables tab along the bottom of the window.
You can then drag a table from the Data pane onto the new layout. Right-click the table, and then select Add related tables from the menu that appears. Doing so includes any table that is related to the original table to the layout.
Create reports
You can create a new report from the data model editing in the service experience by selecting the New report button in the ribbon. This opens a new browser tab to the report editing canvas to a new report that is built on the semantic model.
When you save your new report, you're prompted to choose a workspace, provided you have write permissions for that workspace. If you don't have write permissions, or if you're a free user and the semantic model resides in a Premium-capacity or Fabric F64 or greater workspace, the new report is saved in your My workspace.
AutoSave
As you made changes to your data model, your changes are automatically saved. Changes are permanent with no option to undo.
Permissions
A user must have write and build semantic model permissions in order to open and edit the corresponding data model in the Power BI service.
Enabling data model editing in the admin portal
Power BI administrators can enable or disable data model editing in the service for the entire organization or for specific security groups, using the setting found in the Power BI admin portal, as shown in the following image.
Viewing audit logs and activity events
Power BI administrators can audit operations pertaining to editing data models in the web operations from the Microsoft 365 Admin Center. Audit operations supported for editing data models in the web are the following:
| Friendly name | Operation name | Notes |
|---|---|---|
| Applied a change to model in Power BI | ApplyChangeToPowerBIModel | A user makes a change to an existing model. This occurs whenever any edit is made to the model (example: write a DAX measure, manage relationships, others) |
| Retrieved a model from Power BI | GetPowerBIDataModel | A user opens the Open data model experience or resyncs a data model. |
For more information on accessing your audit logs, see the Access your audit logs article.
Capacity utilization and reporting
You can monitor the effect editing data models in the service has on your Power BI Premium capacities using the Premium metrics app. Capacity effect can be monitored for editing data models in the web using the following operations.
| Operation | Description | Workload | Type |
|---|---|---|---|
| Web Modeling read | A data model read operation in the semantic model web modeling user experience | Semantic models | Interactive |
| Web Modeling write | A data model write operation in the semantic model web modeling user experience | Semantic models | Interactive |
Considerations and limitations
There are a few limitations for this release of editing data models in the Power BI service, which fall into a handful of categories.
Unsupported semantic models
The following scenarios don't support opening the data model for a semantic model in the service:
- Semantic models that have incremental refresh.
- Semantic models that have been deployed by a deployment pipeline.
- Semantic models that haven't yet been upgraded to enhanced metadata format. You can upgrade to enhanced metadata format by opening the corresponding pbix in Desktop and republishing.
- Semantic models that have automatic aggregations configured.
- Semantic models that have a live connection.
- Semantic models migrated from Azure Analysis Services (AAS).
- Not all semantic models in Pro workspaces are currently supported in UAE North.
To see which limitation is preventing you from opening your data model, hover over the Open data model button in the semantic model details page. This displays a tooltip indicating which limitation is causing the Open data model button to be disabled.
Limitations
There are still many functional gaps between the model view in Power BI desktop and service. Functionality not yet supported in the service includes:
- Setting a table as a feature table
- Configuring any feature table properties
- Changing the storage mode of a table
- Changing to and from the data category ‘barcode’
- Connecting to new data sources
- Transforming data using Power Query editor
- View as dialog
- Q&A setup and configuration including editing synonyms
- Classifying sensitivity of your report
- External tools integration
- When modifying your data model within the Service, creating a relationship between DirectQuery tables from the same Power BI semantic model or Azure Analysis Services instance will not be properly prevented. Like Desktop, this scenario is not supported, but no error will be generated when you try to perform this action.
- When modifying your data model within the Service, changing the name of data fields will not automatically update in existing visuals in downstream artifacts that depend on that semantic model.
Semantic models edited with external tools
Utilizing external tools to modify the semantic model using the XMLA endpoint might cause unexpected behavior when editing your semantic model in the web if the write operation is not supported. For more information about supported write operations, please refer to our documentation on changes outside of Power BI.
Accessibility
Full accessibility isn’t currently supported for data model editing in the Power BI service.
Related content
This article provided information about the preview for editing data models in the Power BI service. For more information on data modeling in Power BI, see the following resources: