Power BI is one of the leading data visualisation tools providing quick insights into a company’s performance and allowing data-driven business decisions to be made.
Power BI allows users to collate data from multiple sources and present it in the form of interactive reports accessible through a self-service method. However, the functionality of these reports depends greatly on decisions taken in the data modelling stage of the report’s construction and these decisions will have a significant impact on the report’s efficacy.
Here are 4 features you should be aware of:
1. Benefits of Calculation Groups
Since the introduction of the Tabular Editor Add-In, Calculation Groups can be utilised to eliminate the use of excess measures in your report. Instead of needing to create similar intelligence across multiple KPI’s, a single grouped one can be made. Therefore, to see YoY, WoW and MoM metrics for revenue and sales one “Calculation Group” containing three measures for this time-based intelligence can be substituted in place of six individual measures. Not only does this clean up your report by reducing the number of fields contained but it also improves performance.
2. Future-proofing your reports
Aggregating data is also key to creating a robust, future-proof model. By only aggregating your dataset to the granularity you require in your report, redundant data isn’t pulled, reducing the model size as well as speeding up data refreshes. The benefits of aggregations may not be obvious at first but will be noticeable over time as data accrues and your model grows.
Being scrupulous is key to improving reporting performance. You should go further than aggregating your data by removing unnecessary columns from your data model, especially those with many unique values. This will improve data compression and thus improve performance. Furthermore, any unnecessary Applied Steps in Power Query should be omitted. For example, if you’ve previously sorted your data in Power Query to help you inspect it, remove this step to be certain that Power BI isn’t performing any superfluous tasks.
3. Utilising defaults
As default, Auto Date / Time is enabled in Power BI. Although useful in providing support to build date hierarchies, hidden date tables are created for each date field in your model to do this. A more efficient use of Power BI resources would involve bringing your own date table into your model (imported or created using DAX) and disabling Auto Date / Time. This would allow you to handle your date filtering requirements without your model making inefficient use of resources.
4. Optimising DAX Expression Performance
Even the way in which you calculate your KPI’s can affect your report’s performance. Therefore, it’s vital to maximise the efficiency of DAX expressions which can be achieved by declaring variables. Instead of repeating calculations within an expression, a variable can be declared to define this functionality and can then be called upon later in the expression. For example, in a YoY calculation the best practice would be to declare your prior year calculation as a variable rather than to calculate it twice within the YoY expression. This can drastically reduce the query time as expression logic isn’t unnecessarily repeated, rather a metric is calculated once and later just referred to.
As well as improving query performance, the use of variables helps to improve an expression’s readability which will prove beneficial if the expression needs to be debugged at a later stage, with expression logic able to be checked individually rather than in a nested format.
Conclusion
A robust and efficient data model is the foundation of any good Power BI reporting solution. The above techniques are just some of the many best practices Power BI has to offer for improved reporting performance. By deploying your selection of Power BI’s many tools to optimise report efficiency and usability, you can achieve the optimum reporting solution for your needs.
About Ipsos Jarmany
Ipsos Jarmany is a team of expert Power BI consultants based in London, UK. Contact us to discuss how we can help you visualise your data through Power BI.
References
REDUCE the # of measures with Calculation Groups In Power BI – YouTube
The How and Why of Power BI Aggregations – YouTube
A comprehensive guide to Power BI performance tuning – SQLGene Training
2 ways to reduce your Power BI dataset size and speed up refresh – YouTube
Power BI variables with efficiency and debugging – Learn DAX
DAX: Use variables to improve your formulas – Power BI | Microsoft Docs
