Good performance in terms of loading time is important for improving user acceptance and gaining the trust of stakeholders. That’s why, in this blog, we’ll focus on methods for reducing loading times and improving report performance.
In this blog post, we'll focus on the two most important ways to improve the performance of a Power BI dashboard: the data model and the visualizations in the report.
Data Model
Within a model, you want fields with a specific data type to always be displayed in the same way. For example, with a simple script, you can set the format for all date fields, format all percentages the same way, or display all currency fields with the same number of decimal places. Below is an example of how to set a fixed date format for all date fields.
Optimize the model
You should always keep a Tabular Model as small and clean as possible. By using a number of scripts and methods, you can optimize the data model using Tabular Editor. Here are a few examples to consider:
- Set fields that are used in relationships but not in visualizations to invisible
- Hide fields in the model that are used in measured values but not in visualizations
- Remove unnecessary fields from the model
Notices
When adding new tables or aliases (calculated tables), the default aggregation for the fields is set to “Default.” This means that all numeric fields are summed by default. Of course, this can be quickly and easily adjusted within Tabular Editor itself, but with a script, you can apply this change to the entire model at once, or only to specific cases or certain data types.
Perspectives
Perspectives are a useful feature of a Tabular Model. Within a large model, perspectives can help ensure that the model remains user-friendly and easy to navigate for the customer (e.g., Finance, Supply Chain). When adding fields or new dimensions, updating the perspectives is often overlooked, causing them to become incomplete and increasingly difficult for end users to navigate. A script that cycles through a model’s relationships and adds the tables to the perspectives resolves this issue.
Analyze further
In addition to the methods mentioned above, you can take an even closer look at how your metrics are structured. As the well-known saying goes, “All roads lead to Rome.’ The same applies to DAX calculations. But not all calculations (’paths“) are equally effective. The Performance Analyzer in Power BI can help with this. This tool shows exactly how long it takes to load elements and visuals.
Once you’ve identified the pain points, you can determine which metrics could be improved. With DAX Studio, you can then further analyze the calculations behind these metrics by testing whether other DAX calculations work better to achieve the same result. It’s good to know that DAX Studio is included in Power BI as an extension/add-on.
The Vertipaq Analyzer allows you to examine the data in a more general sense. How much data is there within each column, how diverse is a column, and how many rows of data does it contain?.
Conclusion
In this blog post, I’ll share just a few ways to improve the performance of a Power BI dashboard. The scripting language is incredibly powerful and offers many options for automating and standardizing processes in your model. With just a few standard scripts, you can already fine-tune various aspects of different models, which saves you a lot of work. If necessary, you can perform further analysis using tools like DAX Studio and Vertipaq Analyzer when certain visuals are causing reports to run slowly. For more information on how to optimize a model, see this blog.
