Skip to main content

Blog consacré à Microsoft Power BI

Deep dive into Optimize ribbon and streamline your Power BI authoring

Headshot of article author Zoe Douglas

Product Manager
15 décembre, 2022

Let’s look at how you can streamline your authoring experience in Power BI Desktop with the Optimize ribbon and relationship editing in the properties pane.

  • The Optimize ribbon was announced for public preview in the November 2022 release! With the Optimize ribbon you can fully control when visuals refresh with Pause visuals and Refresh visuals, quickly choose, and apply predefined combinations of settings to meet your specific reporting needs with Optimization presets, and conveniently launch the Performance analyzer to delve deeper into the visual queries themselves, including the SQL queries Direct Query generates.
  • The relationship editing in the properties pane was announced for public preview in the October 2022 release! Edit your relationships right in the properties pane in the Model view without running queries to preview or validate the data. A welcome relief for those using DirectQuery storage mode, as these queries can cause you to wait and impact your data sources.

How do these features work to streamline your authoring experience? Here are some scenarios they can help you with.

1. Make changes to an existing DirectQuery report without running queries until you are ready – Simply click Pause visuals in the Optimize ribbon to have the report enter the paused state. With the report in a paused state, the visuals will only update if they do not need to send a query. If the visual does need to send a query, the visual instead enters a pending state. In a pending state the visual still shows what it looked like before the change was introduced just for context and the tooltips are disabled to stop report tooltips from also sending out queries. Now you can add or remove multiple columns or measures into the field well, re-order the fields, and even filter the visual using the Filter pane. Once you are done making all those changes, you can click Refresh on the visual to all the visual to send a query just once, click Refresh on the banner or ribbon to have all visuals on the page send a query just once, or click Resume visual queries on the banner or Paused visuals in the Optimize ribbon to have all the visuals send a query to update then continue to send queries with any future updates.

Here is the Pause visuals flow:

This animation demonstrates adding the measure Fare and Average fare per trip to two of the visual’s tooltips on the page, reordering them in the field well, and then resuming the visuals from the banner.

Without the Pause visuals each time a measure was added, or moved around in the fields well, a new visual query would have been sent to the DirectQuery data source. That’s 3 queries for each visual, totaling 6 queries sent. Now, with the Pause visuals each time the measure was added, or moved around, no queries were sent. A total of only 2 queries were sent when the report was resumed. For this simple report change not running the 4 extra queries saved time waiting and didn’t waste data source resources.

2. Create new visuals without running a query until you are ready – Again, simply click Pause visuals in the Optimize ribbon to have the report enter the paused state. Now add in a new Matrix visual from the Visualization pane and add Trips, Day of week, and Time of the day to the Matrix visual. The Matrix visual enters a pending state and no queries are sent! Now re-order the fields. Again, no queries are sent! Open the Filter pane, bring in the Year, and filter to the year 2013. At this point seeing the visual will be helpful before making more changes, so I can click on Refresh in the pending visuals banner or open the Selection pane to refresh the visual there. I have only sent 1 query, and the query was pre-filtered, speeding up the return time and reducing the impact on the data source. I can make formatting changes to the visual, and even in the report paused state these show because a DAX query is not needed. Adding a background color formatting change does need a visual query so then visual enters the pending state. I can Refresh or Resume visual queries to see the final Matrix.

This animation shows the creation of a new visual using paused visuals.

Without the Pause visuals, each time a measure or column was added, or moved around in the fields well, a new visual query would have been sent to the DirectQuery data source. That’s 6 queries total before I started formatting and 5 of those were before I had filtered the visual to single year. Now, with the Pause visuals each time the measure or column was added, or moved around, no queries were sent. A total of only 2 queries were sent when the visual was refreshed or the report was resumed, and they were filtered queries. For this new visual addition not only did avoiding the extra queries save time waiting and didn’t waste data source resources, but I was also able to run a smaller, targeted query and to format the visual with minimal disruption.

3. Create measures in a DirectQuery model without the visuals refreshing until you are ready – Again, simply click Pause visuals in the Optimize ribbon to have the report enter the paused state. In a DirectQuery report, anytime a new measure is created all the visuals on the report page will refresh. But, with the report in a paused state I can create new measures and instead all the visuals enter a pending state. I can also add them to the pending visuals. I can then refresh an individual visual to see the new measure or simply resume visual queries when I am done.

This animation shows the creation of 2 new measures while using Pause visuals.

Without the Pause visuals, each time a measure was created all the visuals would have sent queries to the DirectQuery data source. That’s 5 visuals on the page sending 5 queries with each model change for a total of 22 queries when creating 2 measures and adding the two new measures to a single visual! Now, with the Pause visuals, each time the measure was created, or added to a visual, no queries were sent. A total of only 6 queries were sent when the single visual was refreshed, and then the report resumed. For these modeling changes we saw the most reduction in queries and time waiting, as well as load on the data source resources.

4. Quickly edit relationships between DirectQuery tables – Going to the model view in Power BI Desktop, to edit a relationship between two DirectQuery tables I can now simply click on the relationship line and edit it in the Properties pane! I can change the table, column, cardinality, direction, make it active or inactive, and even turn on referential integrity if I am confident each table is not missing values present in the other one. When I am done, I click on apply changes. I can also hold down Ctrl to select multiple relationships to then make bulk changes in the properties pane, or to delete them using the Delete key.

This animation shows how to edit relationships in the properties pane.

Without the edit relationship in the properties pane, each time I edit a relationship in the Edit relationships dialog queries would have run when I opened the dialog to show the data preview and, with every change of table or column, queries would have run to validate the cardinality and direction of the relationship. Now, with the edit relationship in the properties pane, I do not run queries and the relationship is only validated once I am ready and have clicked the apply changes button!

5. Quickly learn about and apply query reduction settings to your DirectQuery report – I can click on the Optimization presets drop down in the Optimize ribbon to choose Query reduction for my report. The dialog explains the applied settings.

Cross-highlighting and cross-filtering is how visuals interact with each other when a data point is clicked. The default is Power BI Desktop is cross-highlighting, but I could have changed the default to cross-filtering by going to File > Options and Settings, then click on Options, select Report settings in CURRENT FILE, and then select the “Change default visual interaction from cross highlighting to cross filtering.”

Optimization presets will only turn off default cross-highlighting and cross-filtering, so any customization done at the visual level with Edit interactions is not impacted. I can click on any visual to show to Format ribbon and then go to Edit interactions. Edit interactions then shows options on all the other visuals in the report so I can decide how they will adjust when a data point is selected in the selected visual — from cross filtering or cross highlighting to not interacting at all.

If I had customized any of the Edit interactions, the Optimization preset disabling cross highlighting and cross filtering by default would not change this customization. Any customized Edit interaction between visuals will still work just as they had before.

Next, Apply buttons are added to the slicers. Every slicer in my report now has an apply button and users can select/unselect multiple options in each single slicer and not have any other visual in the report send a query until the Apply button is clicked. The slicers are still independent of each other, so if I have 2 slicers with Apply buttons, each Apply button would have to be clicked to apply the slicer selections.

Finally, Apply buttons are added to the Filter pane. The Filter pane button added controls all the filter selections in the pane with one Apply button. Learn more at https://learn.microsoft.com/power-bi/create-reports/power-bi-report-filter?tabs=powerbi-desktop#apply-filters-button

This animation shows how to set the Query reduction Optimization preset.

6. Adjust the query reduction settings to the level you need in your DirectQuery report – I can click on the Optimization presets drop down in the Optimize ribbon to choose Customize to adjust any of the settings. In my report the slicers are for single select and the Apply buttons are more useful in multiple select, so I can keep the other settings and return the slicers to instantly apply with Customize.

This animation shows how to set the Customize Optimization preset to revert the slicers to instantly apply.

7. Quickly revert to the full interactivity after a data source change in your report – I can click on the Optimization presets drop down in the Optimize ribbon to choose Interactivity for my report. These are also the default settings in Power BI Desktop, so if you have chosen Query reduction or Customized the Optimization presets this is how you can return to default. The dialog explains the applied settings.

This animation shows how to set the Interactivity Optimization preset.

These call out DirectQuery reporting scenarios specifically, but they can be used in all reporting scenarios. Learn more about all the different reporting scenarios in Power BI at https://learn.microsoft.com/power-bi/create-reports/desktop-optimize-ribbon#reporting-scenarios. For a specific visual, you can determine if it’s using DirectQuery by seeing if it has a Direct query line in the Performance analyzer.

 

So now you have seen how you can streamline your authoring experience in Power BI Desktop with the Optimize ribbon and relationship editing in the properties pane.

Try them out today!