Design Mode in Power BI Desktop

To better support large Power BI datasets with many tables, DAX measures, and potentially complex M query expressions, it seems that a ‘design mode’ experience should be added to Power BI Desktop. This experience would expose many of the granular model properties available in SSDT for Analysis Services Tabular projects as well as introduce new features such as schema focus, multi-select changes, and an M query editor.

The main benefit of design mode would be more productive development and operational support for large, complex datasets. Additionally, there would be greater symmetry between the design experience in SSDT and Power BI thus making it easier for dataset designers to leverage their experience working with both tools.

Since this would be an option enabled via an icon on the toolbar from the Relationships view (Modeling tab), the familiar experience (for business analysts) wouldn’t be disrupted. Certain features of Design Mode could be configured via a Global option similar to the Layout and Data Preview options for Query Editor.

Design Mode Solutions

Scenario: Large, Complex Datasets

It’s very common to have datasets with 20+ tables and thus many relationships with a mix of single and bidirectional cross-filtering. With Power BI Premium capacity and roadmap features (ie incremental refresh), it’s increasingly feasible for these datasets to store very large amounts of data. Additionally, expected improvements in DirectQuery performance will also increase the number of large, complex Power BI datasets deployed.

Solution: Design mode would enable the dataset designer to ‘focus’ on one schema (typically a star schema) and thus a subset of the tables in the overall dataset. For example, if 7 tables are related to a Sales fact table, 6 tables are related to a Shipping table, and 5 tables are related to a Budget table, design mode would let you easily select one of the three schemas to work with in isolation.

The schema selected (focused on) would be centered in the Relationships view and other schemas would be invisible or faded into the background, temporarily. Metadata panels on the right would provide easy access to measures, hierarchies, and relationships exclusive to this schema. You might think of the Spotlight feature released in October for report visuals – the schema selected would be spotlighted.

Additionally, in design mode you’d be able author and edit these objects (the Tabular Object Model). For example, the DAX Editor window from SSDT or something close to it would be visible at the top of the relationships view and you could access field descriptions for measures and columns on the right. You could create hierarchies in design mode rather than the Report view too.

Scenario: Manual Measure Authoring and Editing

It’s very common to need to replicate one particular DAX expression pattern for several ‘base’ measures. A simple example is when you need a year-to-date measure created for sales, cost, and margin. In most scenarios, all the author is doing is replacing the expression parameter of the CALCULATE() function with a different base measure. At a minimum this is a tedious, inefficient process. It could also lead to errors.

Solution: In design mode, there would be a simple wizard/UI for identifying the ‘source expression’, the ‘target expression(s)’, and previewing the new measures to be created. Ideally, formatting of the expressions would be applied as with DAX Formatter.

A closely related feature would be multi-select. For example, a single currency format with two decimal places could be applied to several measures as one, simple metadata operation. Likewise, several measures could be assigned to a specific display folder or subfolder as one operation.

Scenario: Asymmetric Design Experience

Display folders are a very common and critical part of Analysis Services models, particularly large models, as they simplify the self-service experience for users. KPIs are also a common and important part of Analysis Services models with their built-in base to target logic and conditional formatting. These two features are not available to Power BI Desktop though there are some work-around options to limit this gap. Nonetheless, without display folders, it’s more difficult to maintain a simple self-service user experience when accessing a Power BI dataset.

Solution: Both display folders and KPIs would become available to dataset designers in design mode. Importantly, the dataset designer should be able to multi-select measures to assign them to a specific folder in one operation. A simple tree view of display folders (and subfolders) would be available on the right that mirrors how the user will see the folders from the report view. Ideally, the Analysis Services KPIs should be improved and modernized before coming to Power BI Desktop so this could be pushed off (back log).

Note: I’ve written about some of these workarounds in my Power BI books. The Power KPI Visual is being featured in my new book.

Scenario: Custom M Query Authoring

The Query Editor graphical interface is great for getting started quickly but, dataset designers often need to customize their M queries. In the current state, the ‘Advanced Editor’ window for writing these queries has no IntelliSense, colorization, or other development features available to DAX expressions.

Solution: From design mode, with and without schema focus enabled, you could access a new M Editor window. You could right-click a table icon in the Relationships view to access the M expression for this given table or use one of the panels on the right. You wouldn’t have to jump to Query Editor to write or edit the M query for a table or to create a new shared M expression.

Ideally there would be some T-SQL authoring support built-in as well for the Value.NativeQuery() function. Like KPIs mentioned earlier, this could be pushed off into the backlog for a future update to design mode.

Note: I’ve blogged about M query development support via VS Code and Visual Studio before.

Wrapping Up

Why blog about something that doesn’t exist?

1. Because blogging increases the probability that some degree or variation of these ideas will make there way to the product.
2. Blog posts which point out limitations or issues with the product are in low supply.
3. I just enjoy to think and write about these topics and I think it’s healthy to do so.

You shouldn’t interpret this post as though I’m 100% confident in all the ideas or that I have some grand, complete vision of what the tooling would or should look like over some period of time. It’s just a blog post highlighting several topics/features I find relevant and you’re welcome to share your feedback.

Next week’s blog: TBD