Power BI Hierarchies performance tips and tricks

Today we are excited to talk about some tips and tricks for improving load performance of hierarchies in Power BI. Hierarchies in scorecards is a hugely powerful tool, so let’s talk about how to optimize content load times for your users and make cascading scorecards as useful as possible. First, some background.

What is ‘hierarchies’?

Power BI Metrics enables ‘cascading scorecards’ that roll up along hierarchies in your data. You can set up a hierarchy for a scorecard and map the Power BI datasets referenced by your metrics to the hierarchy levels and owner fields, automatically creating a new scorecard view for each slice of your data. That’s potentially thousands of automated scorecard views with just a few clicks.

Power BI will cascade connected metric values to each level of the hierarchy. Users can easily drill into the hierarchy to see progress, statuses and do check-ins at different levels. In the images below, you can see the different levels of a project hierarchy in the slicer, and as you navigate to each level or sublevel of the hierarchy, your metric values, statuses, owners, and progress will change along with it.

For full information about how to set up a hierarchy for your org, and if hierarchies could be right for your scenario, see our documentation. Get inspired!

Scenarios: When to use hierarchies in Power BI scorecards – Power BI | Microsoft Learn

Tips and Tricks:

Now, with great hierarchies comes great data quantities. For context, each time you select an option from the slicer dropdown, we query the connected datasets to bring in the current value and all the historical data so that we can build a nice sparkline and history for each slice of data you want to look at. That’s potentially a lot of querying, but we have our top 5 tips and tricks to help improve your performance when using hierarchies.

1. Enable query caching

Enabling query caching on connected datasets in premium workspaces could significantly speed up content loading by caching previously used query results. The setting is applied at the dataset level so any reports using this dataset can benefit from the cache. This feature is not applicable to LiveConnect datasets leveraging Azure Analysis Services or SQL Server Analysis Services. Note: the connected datasets must be in premium workspaces to enable query caching, not just the scorecard.

For more info on query caching, visit our documentation.

Query caching in Power BI Premium – Power BI | Microsoft Learn

2. Relative date filters

Building relative date filters into the connected visuals could improve load performance by limiting the number of datapoints, and therefore queries, that need to be performed for each selection of the hierarchy to load. Rendering history for 1 data point every day for 2 years, for 100 metrics is much more data than rendering 1 data point every day for the past 2 months. If your metric history doesn’t need to be preserved from the inception of the datapoint, relative date filters may be a nice way to improve performance.

For more information on relative date filters, see our documentation.

Create a relative date slicer or filter in Power BI – Power BI | Microsoft Learn

3. Connect to drilled down/expanded visual data

Similar to relative date filters, one way to bring in less data and potentially improve content load times is to build a date hierarchy on the visual you intend to connect to, and drill to only the date granularity you need. Then, you can connect to the visual with the date granularity you need. For example, instead of connecting to the entire time series which contains 1 data point per day, you could drill to a monthly aggregation, and bring in far less data.

For more information on drill + expand, see our documentation.

Drill mode in the Power BI service – Power BI | Microsoft Learn

4. Adjust periodicity (time period) for metrics to no cycle or a lesser time period

Adjust the time period on your metric(s) by opening the details pane in edit mode and navigating to the time period section. This control will change the time period that shows in the sparkline. We will still take the last 7 data points, but with a setting like “no cycle” the time period for the data points may span less time, which could improve performance.

For more information, see our documentation.

Get started with metrics in Power BI – Power BI | Microsoft Learn

5. Connect to a singular data point rather than a time series

Connecting to a singular data point instead of bringing in the history as you connect could improve performance as well, again, by limiting the data that needs to be rendered each time a user loads the scorecard. If the entire history of the data point in not needed, you can connect to a singular data point and build history from there.

For more information, see our documentation.

Create connected metrics in Power BI – Power BI | Microsoft Learn

There you have it! Our top 5 tips for improving hierarchy load performance. Please let us know in the comments how hierarchies is working for you, and if this was helpful content! Cheers.