02 Aug 2023 Data Model Editing in Power BI Service

Let’s imagine you want to modify or enhance your data model after publishing it to the Power BI service. Until now, you had to go back to Power BI Desktop, make your changes, and republish the dataset. This process could be pretty tedious, and tricky when working with others on the same data model.

Luckily, Microsoft has introduced a new feature that lets you edit your data models in the Power BI service, allowing you to make changes to your existing data models on the web, such as managing relationships, utilising DAX in your browser, and handling row-level security (RLS). You can also collaborate with other users on the same data model at the same time.

In this blog post, we will see how to enable this feature, what it looks like in practice, and run through some important aspects and limitations to keep in mind.

Getting Started

Enabling the Feature

To use the preview feature in a collaborative workspace, all you need to do is select Workspace Settings > Power BI > General, and then check Users can edit data models in the Power BI service (preview).

Note: The feature is enabled by default in My Workspace.

Figure 1: Enabling the feature.

Opening the Data Model

You can access the data model in several ways:

• From the dataset “More Options” dropdown list (in the workspace or datahub)
• From the top ribbon after opening the dataset details page
• From the top ribbon when editing a report connected to your dataset

Either way, you should see the following layout:

Figure 2: Data model editing interface on the web.

As you may have noticed, we are greeted with a warning message telling us any changes we make will be permanent and automatically saved — something to remember when editing a data model, especially in a production environment.

Setting Relationships

For simplicity’s sake, the model we are working with includes sample financial data along with a date table.

Let’s try to create a relationship with a simple drag-and-drop from the Date table to the Financials table using the Date field:

Figure 3: Error when creating a one-to-many relationship.

As you can see, the web data model editor does not allow the “From” cardinality to be set to one unless the relationship is one-to-one. In other words, you can’t create a relationship from dimension to fact – it has to be the other way around, from fact to dimension:

Figure 4: Creating a many-to-one relationship.

Note: Relationship autodetection is not supported in the data model web editor.

DAX Editing on the Web

The most noteworthy aspect of the new feature is the ability to use DAX in the Power BI service, which means you will be able to see and edit the DAX you created in Power BI Desktop, and also create new measures, calculated columns, and calculated tables straight from your browser!

Measures

When creating a measure by selecting “New Measure” in the ribbon, we get the same editing experience as in Power BI Desktop, including autocompletion for DAX functions.

Once created, we can find the measure on the right under the relevant table:

Figure 5: Creating a measure.

Calculated Columns

Following the same process, you can just as easily create calculated columns using DAX:

Figure 6: Creating a calculated column.

Calculated Tables

Last but not least, you can also create new tables using the DAX editor, just like you would in Power BI Desktop. You can then find the table you created on the right with a specific icon letting you know it is indeed a calculated table, not one from the source model.

Figure 7: Creating a calculated table.

Speaking of calculated or source objects, remember that you can’t rename or delete columns and tables that are part of the source model, you can only do that for calculated objects.

Handling Field Properties

However, it is possible to modify field properties, even for source tables or columns. The Properties pane on the right resembles the one from Power BI Desktop, giving you control over:

• Description
• Display folder
• Hidden status
• Format
• Data type
• Data category

The following properties are greyed out and cannot be altered:

• Feature table
• Sort by (for a column)
• Table storage mode

Managing Row-Level Security (RLS)

When editing your data model on the web, you can also manage RLS by creating roles and assigning users to them from your browser. All you have to do is select “Manage roles” in the ribbon and it will take you to a dedicated window where you can:

• Define or delete roles using either the interface or the DAX editor
• Assign users to roles by entering email addresses

Figure 8: Creating a new role.

Creating a Report

Finally, once you have finished working on your data model, you can create a report by clicking on the last button in the ribbon, “New report”.

This will take you to the web report editor that was already there before this feature, so there’s nothing new to explain.

What About Version Control?

Since this feature allows collaboration, you may wonder what happens when multiple users try to edit the same data model simultaneously.

According to Microsoft, this feature behaves similarly to the multi-author experience for Power BI Datamarts, with a basic “first step” approach: when one user makes a change, the others will have to refresh their model to get the update and be able to make further changes.

Conclusion

With this new ability of editing data models on the web, it may look like we are moving away from desktop towards doing more and more things in-browser. But as we have seen, this new feature comes with a few caveats and limitations, not to mention the following functionalities which are not yet supported:

• Connecting new data sources
• Transforming data in Power Query
• External tools such as Tabular Editor

All in all, editing data models in the Power BI service offers a convenient and collaborative way to modify your existing data models on the web, but Power BI Desktop is still king when it comes to extensive, complex report design.

If you’d like to understand how to get the most out of your Power BI and Microsoft stack, simply drop us a line and our team of certified experts will be happy to help!